﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;


namespace QLNSService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "QLNSService" in both code and config file together.
    public class QLNSService : IQLNSService
    {
       
       //--------------------------DON VI HANH CHINH---------------------------
        
        public List<DONVI_HANHCHINH> getDVHC()
        {
            DataTable dt = new DataTable(); // khai báo bảng dt
            sqlHelper.Fill(dt, "select *from DONVI_HANHCHINH");
            List<DONVI_HANHCHINH> listDVHC = new List<DONVI_HANHCHINH>();
            
            foreach (DataRow dr in dt.Rows)
            {
                DONVI_HANHCHINH dvhc = new DONVI_HANHCHINH();
                dvhc.Ma_DV_HC = dr[0].ToString();
                dvhc.TenDVHC = dr[1].ToString();
                listDVHC.Add(dvhc);
            }
            return listDVHC;
         }



        public int addDVHC(DONVI_HANHCHINH dvhc)
        {
            SqlParameter[] pr = new SqlParameter[2]; // khai báo thiết lập số cột theo số cột trong CSDL
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char); // khai báo biến nhận giá trị "Ma_DV_HC" do người dùng nhập vào
            pr[0].Value = dvhc.Ma_DV_HC; // chỉ ra đối tương của lớp nhận giá trị cho cột pr[0]
            pr[1] = new SqlParameter("@TenDVHC", SqlDbType.NVarChar); // Khai báo biến nhận giá trị "TenDVHC" do người dùng nhập vào
            pr[1].Value = dvhc.TenDVHC; // Chỉ ra đối tượng của lớp nhận gía trị cho cột pr[1]


            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr1[0].Value = dvhc.Ma_DV_HC;
            sqlHelper.Fill(check, "select Ma_DV_HC from DONVI_HANHCHINH where Ma_DV_HC=@Ma_DV_HC",pr1);

            int kq;
            if (check.Rows.Count==0)
            {
                string sql = "insert DONVI_HANHCHINH (Ma_DV_HC, TenDVHC) values (@Ma_DV_HC, @TenDVHC)";
                kq = sqlHelper.ExecuteNonQuery(sql, pr);
                return kq;
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateDVHC(DONVI_HANHCHINH dvhc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = dvhc.Ma_DV_HC;
            pr[1] = new SqlParameter("@TenDVHC", SqlDbType.NVarChar);
            pr[1].Value = dvhc.TenDVHC;
            int kq=sqlHelper.ExecuteNonQuery("update DONVI_HANHCHINH set TenDVHC=@TenDVHC where Ma_DV_HC=@Ma_DV_HC",pr);
            return kq;
        }

        public void deleteDVHC(string Ma_DV_HC)
        {
            SqlParameter[] pr=new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = Ma_DV_HC;
            sqlHelper.ExecuteNonQuery("delete from DONVI_HANHCHINH where Ma_DV_HC=@Ma_DV_HC",pr);
        }


        public List<DONVI_HANHCHINH> getLastDVHC()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select TOP 1 Ma_DV_HC from DONVI_HANHCHINH ORDER BY Ma_DV_HC DESC");
            List<DONVI_HANHCHINH> listLastID = new List<DONVI_HANHCHINH>();

            foreach (DataRow dr in dt.Rows)
            {
                DONVI_HANHCHINH dvhc = new DONVI_HANHCHINH();
                dvhc.Ma_DV_HC = dr[0].ToString();

                listLastID.Add(dvhc);
            }
            return listLastID;
        }

        //------------------------------KY LUAT----------------------------------

        public List<KY_LUAT> getKL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from KY_LUAT");
            List<KY_LUAT> listKL = new List<KY_LUAT>();
            foreach (DataRow dr in dt.Rows)
            {
                KY_LUAT kl = new KY_LUAT();
                kl.Ma_KL = Convert.ToInt32(dr[0].ToString());
                kl.DienGiai = dr[1].ToString();
                listKL.Add(kl);
            }
            return listKL;
        }


        public int addKL(KY_LUAT kl)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = kl.Ma_KL;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = kl.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert KY_LUAT (Ma_KL, DienGiai) values (@Ma_KL, @DienGiai)", pr);
            return kq;
        }

        public int updateKL(KY_LUAT kl)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KL",SqlDbType.Int);
            pr[0].Value = kl.Ma_KL;
            pr[1] = new SqlParameter("@DienGiai",SqlDbType.NVarChar);
            pr[1].Value = kl.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update KY_LUAT set DienGiai=@DienGiai where Ma_KL=@Ma_KL",pr);
            return kq;
        }

        public void deleteKL(string Ma_KL)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            sqlHelper.ExecuteNonQuery("delete from KY_LUAT where Ma_KL=@Ma_KL",pr);
        }


        //--------------------KHEN THUONG ------------------------

        public List<KHEN_THUONG> getKT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from KHEN_THUONG");
            List<KHEN_THUONG> listKT = new List<KHEN_THUONG>();
            foreach(DataRow dr in dt.Rows)
            {
                KHEN_THUONG kt = new KHEN_THUONG();
                kt.Ma_KT = Convert.ToInt32(dr[0].ToString());
                kt.DienGiai = dr[1].ToString();
                listKT.Add(kt);
            }
            return listKT;
        }

        public int addKT(KHEN_THUONG kt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = kt.Ma_KT;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = kt.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert KHEN_THUONG (Ma_KT, DienGiai) values (@Ma_KT, @DienGiai)",pr);
            return kq;
        }

        public int updateKT(KHEN_THUONG kt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KT",SqlDbType.Int);
            pr[0].Value = kt.Ma_KT;
            pr[1] = new SqlParameter("@DienGiai",SqlDbType.NVarChar);
            pr[1].Value = kt.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update KHEN_THUONG set DienGiai=@DienGiai where Ma_KT=@Ma_KT",pr);
            return kq;
        }

        public void deleteKT(string Ma_KT)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_KT",SqlDbType.Int);
            pr[0].Value = Ma_KT;
            sqlHelper.ExecuteNonQuery("delete from KHEN_THUONG where Ma_KT=@Ma_KT",pr);
        }

        //------------TON GIAO--------------------

        public List<TON_GIAO> getTG()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from TON_GIAO");
            List<TON_GIAO> listTG = new List<TON_GIAO>();
            foreach (DataRow dr in dt.Rows)
            {
                TON_GIAO tg = new TON_GIAO();
                tg.Ma_TG = Convert.ToInt32(dr[0].ToString());
                tg.TenTG = dr[1].ToString();
                listTG.Add(tg);
            }
            return listTG;
        }

        public int addTG(TON_GIAO tg)
        {
            SqlParameter[] pr=new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TG",SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            pr[1] = new SqlParameter("@TenTG",SqlDbType.NVarChar);
            pr[1].Value = tg.TenTG;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert TON_GIAO (Ma_TG, TenTG) values (@Ma_TG, @TenTG)",pr);
            return kq;
        }

        public int updateTG(TON_GIAO tg)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TG",SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            pr[1] = new SqlParameter("@TenTG",SqlDbType.NVarChar);
            pr[1].Value = tg.TenTG;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update TON_GIAO set TenTG=@TenTG where Ma_TG=@Ma_TG",pr);
            return kq;
        }

        public void deleteTG(string Ma_TG)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[0].Value = Ma_TG;
            sqlHelper.ExecuteNonQuery("delete from TON_GIAO where Ma_TG=@Ma_TG",pr);
        }


        public List<TON_GIAO> getLastTG()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select TOP 1 Ma_TG from TON_GIAO ORDER BY Ma_TG DESC");
            List<TON_GIAO> listLastTG = new List<TON_GIAO>();
            foreach (DataRow dr in dt.Rows)
            {
                TON_GIAO tg = new TON_GIAO();
                tg.Ma_TG = Int32.Parse(dr[0].ToString());
                listLastTG.Add(tg);
            }
            return listLastTG;
        }

        //----------------DAN TOC-----------------------

       

        public List<DAN_TOC> getLastDT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select TOP 1 Ma_DT from DAN_TOC ORDER BY Ma_DT DESC");
            List<DAN_TOC> listLastDT = new List<DAN_TOC>();
            foreach (DataRow dr in dt.Rows)
            {
                DAN_TOC dtoc = new DAN_TOC();
                dtoc.Ma_DT = Int32.Parse(dr[0].ToString());
                listLastDT.Add(dtoc);
            }
            return listLastDT;
        }


        public List<DAN_TOC> getDT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from DAN_TOC");
            List<DAN_TOC> listDT = new List<DAN_TOC>();
            foreach (DataRow dr in dt.Rows)
            {
                DAN_TOC dtoc = new DAN_TOC();
                dtoc.Ma_DT = Convert.ToInt32(dr[0].ToString());
                dtoc.TenDT = dr[1].ToString();
                listDT.Add(dtoc);
            }
            return listDT;
        }

        public int addDT(DAN_TOC dt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[0].Value = dt.Ma_DT;
            pr[1] = new SqlParameter("@TenDT",SqlDbType.NVarChar);
            pr[1].Value = dt.TenDT;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert DAN_TOC (Ma_DT, TenDT) values (@Ma_DT, @TenDT)",pr);
            return kq;
        }

        public int updateDT(DAN_TOC dt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[0].Value = dt.Ma_DT;
            pr[1] = new SqlParameter("@TenDT", SqlDbType.NVarChar);
            pr[1].Value = dt.TenDT;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update DAN_TOC set TenDT=@TenDT where Ma_DT=@Ma_DT",pr);
            return kq;
        }

        public void deleteDT(string Ma_DT)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DT",SqlDbType.Int);
            pr[0].Value = Ma_DT;
            sqlHelper.ExecuteNonQuery("delete from DAN_TOC where Ma_DT=@Ma_DT",pr);
        }

        //---------------GIA DINH CHINH SACH-------------------

        public List<GDCS> getGDCS()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from GD_CHINHSACH");
            List<GDCS> listGDCS = new List<GDCS>();
            foreach (DataRow dr in dt.Rows)
            {
                GDCS gdcs = new GDCS();
                gdcs.Stt_GD_CS = Convert.ToInt32(dr[0].ToString());
                gdcs.DienGiai = dr[1].ToString();
                listGDCS.Add(gdcs);
            }
            return listGDCS;
        }

        public int addGDCS(GDCS gdcs)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_GD_CS",SqlDbType.Int);
            pr[0].Value = gdcs.Stt_GD_CS;
            pr[1] = new SqlParameter("@DienGiai",SqlDbType.NVarChar);
            pr[1].Value = gdcs.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert GD_CHINHSACH (Stt_GD_CS, DienGiai) values (@Stt_GD_CS, @DienGiai)",pr);
            return kq;
        }

        public int updateGDCS(GDCS gdcs)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[0].Value = gdcs.Stt_GD_CS;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = gdcs.DienGiai;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update GD_CHINHSACH set DienGiai=@DienGiai where Stt_GD_CS=@Stt_GD_CS", pr);
            return kq;
        }

        public void deleteGDCS(string Stt_GD_CS)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Stt_GD_CS",SqlDbType.Int);
            pr[0].Value = Stt_GD_CS;
            sqlHelper.ExecuteNonQuery("delete from GD_CHINHSACH where Stt_GD_CS=@Stt_GD_CS",pr);
        }


        //---------------------------NGACH----------------------------

        public List<NGACH> getNgach()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from NGACH");
            List<NGACH> listNgach = new List<NGACH>();
            foreach(DataRow dr in dt.Rows)
            {
                NGACH ng = new NGACH();
                ng.Ma_Ngach = dr[0].ToString();
                ng.TenNgach = dr[1].ToString();
                ng.Ma_Loai_Ngach = dr[2].ToString();
                listNgach.Add(ng);
            }
            return listNgach;
        }

        public int addNgach(NGACH ng)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[0].Value = ng.Ma_Ngach;
            pr[1] = new SqlParameter("@TenNgach", SqlDbType.NVarChar);
            pr[1].Value = ng.TenNgach;
            pr[2] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[2].Value = ng.Ma_Loai_Ngach;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_Ngach",SqlDbType.Char);
            pr1[0].Value = ng.Ma_Ngach;
            sqlHelper.Fill(check, "select Ma_Ngach from NGACH where Ma_Ngach=@Ma_Ngach", pr1);
            int kq;
            if (check.Rows.Count == 0)
            {
                
                kq = sqlHelper.ExecuteNonQuery("insert NGACH (Ma_Ngach, TenNgach, Ma_Loai_Ngach) values (@Ma_Ngach, @TenNgach, @Ma_Loai_Ngach)", pr);
            }
            else
            {
                kq= -1;
            }
            return kq;
        }

        public int updateNgach(NGACH ng)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[0].Value = ng.Ma_Ngach;
            pr[1] = new SqlParameter("@TenNgach", SqlDbType.NVarChar);
            pr[1].Value = ng.TenNgach;
            pr[2] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[2].Value = ng.Ma_Loai_Ngach;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update NGACH set TenNgach=@TenNgach, Ma_Loai_Ngach=@Ma_Loai_Ngach where Ma_Ngach=@Ma_Ngach", pr);
            return kq;

        }

        public void deleteNgach(string Ma_Ngach)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[0].Value = Ma_Ngach;
            sqlHelper.ExecuteNonQuery("delete from NGACH where Ma_Ngach=@Ma_Ngach",pr);
        }


        //----------------------BAC LUONG-------------------------

        public List<BAC_LUONG> getBL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from BAC_LUONG");
            List<BAC_LUONG> listBL = new List<BAC_LUONG>();
            foreach (DataRow dr in dt.Rows)
            {
                BAC_LUONG bl = new BAC_LUONG();
                bl.Ma_BL = Int32.Parse(dr[0].ToString());
                listBL.Add(bl);
            }
            return listBL;
        }

        public int addBL(BAC_LUONG bl)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[0].Value = bl.Ma_BL;
            

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_BL",SqlDbType.Int);
            pr1[0].Value = bl.Ma_BL;
            sqlHelper.Fill(check,"select Ma_BL from BAC_LUONG where Ma_BL=@Ma_BL",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert BAC_LUONG (Ma_BL) values (@Ma_BL)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateBL(BAC_LUONG bl)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[0].Value = bl.Ma_BL;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update BAC_LUONG set TenBL=@TenBL where Ma_BL=@Ma_BL", pr);
            return kq;
        }

        public void deleteBL(string Ma_BL)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_BL",SqlDbType.Int);
            pr[0].Value = Ma_BL;
            sqlHelper.ExecuteNonQuery("delete from BAC_LUONG where Ma_BL=@Ma_BL",pr);
        }

        public List<BAC_LUONG> getLastBL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select TOP 1 Ma_BL from BAC_LUONG ORDER BY Ma_BL DESC");
            List<BAC_LUONG> listLastBL = new List<BAC_LUONG>();
            foreach(DataRow dr in dt.Rows)
            {
                BAC_LUONG bl = new BAC_LUONG();
                bl.Ma_BL = Int32.Parse(dr[0].ToString());
                listLastBL.Add(bl);
            }
            return listLastBL;
        }

        //---------------------HE SO LUONG-----------------------

        public List<HESO_LUONG> getHSL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from HESO_LUONG");
            List<HESO_LUONG> listHSL = new List<HESO_LUONG>();
            foreach (DataRow dr in dt.Rows)
            {
                HESO_LUONG hsl = new HESO_LUONG();
                hsl.Ma_Loai_Ngach = dr[0].ToString();
                hsl.Ma_BL = Int32.Parse(dr[1].ToString());
                hsl.Ma_HS_Luong = float.Parse(dr[2].ToString());
                listHSL.Add(hsl);
            }
            return listHSL;
        }

        public int addHSL(HESO_LUONG hsl)
        {
            SqlParameter[] pr = new SqlParameter[3];

            pr[0] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[0].Value = hsl.Ma_Loai_Ngach;
            pr[1] = new SqlParameter("@Ma_BL",SqlDbType.Int);
            pr[1].Value = hsl.Ma_BL;
            pr[2] = new SqlParameter("@Ma_HS_Luong", SqlDbType.Decimal);
            pr[2].Value = hsl.Ma_HS_Luong;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[2];
            pr1[0] = new SqlParameter("@Ma_Loai_Ngach",SqlDbType.Char);
            pr1[0].Value = hsl.Ma_Loai_Ngach;
            pr1[1] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr1[1].Value = hsl.Ma_BL;
            sqlHelper.Fill(check,"select Ma_Loai_Ngach, Ma_BL from HESO_LUONG where Ma_Loai_Ngach=@Ma_Loai_Ngach and Ma_BL=@Ma_BL",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert HESO_LUONG (Ma_Loai_Ngach, Ma_BL, Ma_HS_Luong) values (@Ma_Loai_Ngach, @Ma_BL, @Ma_HS_Luong)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateHSL(HESO_LUONG hsl)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[0].Value = hsl.Ma_Loai_Ngach;
            pr[1] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[1].Value = hsl.Ma_BL;
            pr[2] = new SqlParameter("@Ma_HS_Luong", SqlDbType.Decimal);
            pr[2].Value = hsl.Ma_HS_Luong;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update HESO_LUONG set Ma_HS_Luong=@Ma_HS_Luong where Ma_Loai_Ngach=@Ma_Loai_Ngach and Ma_BL=@Ma_BL", pr);
            return kq;
        }

        public void deleteHSL(HESO_LUONG hsl)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_HS_Luong", SqlDbType.Decimal);
            pr[0].Value = hsl.Ma_HS_Luong;
            sqlHelper.ExecuteNonQuery("delete from HESO_LUONG where Ma_HS_Luong=@Ma_HS_Luong",pr);
        }


        //-------------DON VI TRUC THUOC---------------

        public List<DANHMUC_DONVI> getDMDV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from DANHMUC_DONVI");
            List<DANHMUC_DONVI> listDVTT = new List<DANHMUC_DONVI>();
            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dvtt = new DANHMUC_DONVI();
                dvtt.Ma_DV = dr[0].ToString();
                dvtt.TenDVTT = dr[1].ToString();
                dvtt.Ma_DV_CQ = dr[2].ToString();
                dvtt.Ma_Loai_DV = dr[3].ToString();
                listDVTT.Add(dvtt);
            }
            return listDVTT;
        }

        public int addDMDV(DANHMUC_DONVI dvtt)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = dvtt.Ma_DV;
            pr[1] = new SqlParameter("@TenDVTT", SqlDbType.NVarChar);
            pr[1].Value = dvtt.TenDVTT;
            pr[2] = new SqlParameter("@Ma_DV_CQ", SqlDbType.Char);
            pr[2].Value = dvtt.Ma_DV_CQ;
            pr[3] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[3].Value = dvtt.Ma_Loai_DV;

            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[0].Value = dvtt.Ma_DV;
            DataTable check = new DataTable();
            sqlHelper.Fill(check, "select Ma_DV from DANHMUC_DONVI where Ma_DV=@Ma_DV", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert DANHMUC_DONVI (Ma_DV, TenDV, Ma_DV_CQ, Ma_Loai_DV) values (@Ma_DV, @TenDVTT, @Ma_DV_CQ, @Ma_Loai_DV)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateDMDV(DANHMUC_DONVI dvtt)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = dvtt.Ma_DV;
            pr[1] = new SqlParameter("@TenDVTT", SqlDbType.NVarChar);
            pr[1].Value = dvtt.TenDVTT;
            pr[2] = new SqlParameter("@Ma_DV_CQ", SqlDbType.Char);
            pr[2].Value = dvtt.Ma_DV_CQ;
            pr[3] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[3].Value = dvtt.Ma_Loai_DV;
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update DANHMUC_DONVI set TenDV=@TenDVTT, Ma_DV_CQ=@Ma_DV_CQ, Ma_Loai_DV=@Ma_Loai_DV where Ma_DV=@Ma_DV", pr);
            return kq;
        }

        public void deleteDMDV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            sqlHelper.ExecuteNonQuery("delete from DANHMUC_DONVI where Ma_DV=@Ma_DV", pr);
        }

        public List<CHUC_VU> getCV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from CHUC_VU");
            List<CHUC_VU> listCV = new List<CHUC_VU>();
            foreach (DataRow dr in dt.Rows)
            {
                CHUC_VU cv = new CHUC_VU();
                cv.Ma_CV = dr[0].ToString();
                cv.TenCV = dr[1].ToString();
                cv.HeSoCV = float.Parse(dr[2].ToString());
                cv.Ma_Loai_DV = dr[3].ToString();
                listCV.Add(cv);
            }
            return listCV;
        }

        public int addCV(CHUC_VU cv)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[0].Value = cv.Ma_CV;
            pr[1] = new SqlParameter("@TenCV", SqlDbType.NVarChar);
            pr[1].Value = cv.TenCV;
            pr[2] = new SqlParameter("@HeSoCV", SqlDbType.Float);
            pr[2].Value = cv.HeSoCV;
            pr[3] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[3].Value = cv.Ma_Loai_DV;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert CHUC_VU (Ma_CV, TenCV, HeSoCV, Ma_Loai_DV) values (@Ma_CV, @TenCV, @HeSoCV, @Ma_Loai_DV)",pr);
            return kq;
        }

        public int updateCV(CHUC_VU cv)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[0].Value = cv.Ma_CV;
            pr[1] = new SqlParameter("@TenCV", SqlDbType.NVarChar);
            pr[1].Value = cv.TenCV;
            pr[2] = new SqlParameter("@HeSoCV", SqlDbType.Float);
            pr[2].Value = cv.HeSoCV;
            pr[3] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[3].Value = cv.Ma_Loai_DV;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CHUC_VU set TenCV=@TenCV, HeSoCV=@HeSoCV, Ma_Loai_DV=@Ma_Loai_DV where Ma_CV=@Ma_CV", pr);
            return kq;
        }

        public void deleteCV(string Ma_CV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[0].Value = Ma_CV;
            sqlHelper.ExecuteNonQuery("delete from CHUC_VU where Ma_CV=@Ma_CV",pr);
        }


        public List<CHUC_VU> getLastCV()
        {
            DataTable dtLastID = new DataTable();
            sqlHelper.Fill(dtLastID, "select TOP 1 Ma_CV from CHUC_VU ORDER BY  Ma_CV DESC");

            List<CHUC_VU> listLastID = new List<CHUC_VU>();

            foreach (DataRow dr in dtLastID.Rows)
            {
                CHUC_VU cv = new CHUC_VU();
                cv.Ma_CV = dr[0].ToString();

                listLastID.Add(cv);
            }
            return listLastID;
        }

        public List<CANBO_CHUCVU> getCBCV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from CANBO_CHUCVU");
            List<CANBO_CHUCVU> listCBCV = new List<CANBO_CHUCVU>();
            foreach(DataRow dr in dt.Rows)
            {
                CANBO_CHUCVU cbcv = new CANBO_CHUCVU();
                cbcv.Ma_CB = dr[0].ToString();
                cbcv.Ma_CV = dr[1].ToString();
                cbcv.Ma_DV = dr[2].ToString();
                cbcv.QuyetDinh = dr[3].ToString();
                cbcv.NgayKy = Convert.ToDateTime(dr[4]);
                cbcv.FileQuyetDinh = (byte[])dr[5];
                //cbcv.TrangThaiCV = bool.Parse(dr[6].ToString());

                listCBCV.Add(cbcv);
                
            }
            return listCBCV;
        }



       // public static byte[] ConvertImageToArray(System.Drawing.Image imageConvert, System.Drawing.Imaging.ImageFormat formatOfImage)

        public int addCBCV(CANBO_CHUCVU cbcv)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbcv.Ma_CB;
            pr[1] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[1].Value = cbcv.Ma_CV;
            pr[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[2].Value = cbcv.Ma_DV;
            
            pr[3] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[3].Value = cbcv.QuyetDinh;
            pr[4] = new SqlParameter("@NgayKy", SqlDbType.Date);
            pr[4].Value = cbcv.NgayKy;
            pr[5] = new SqlParameter("@FileQuyetDinh", SqlDbType.Binary);
            pr[5].Value = cbcv.FileQuyetDinh;
            
            
            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[3];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cbcv.Ma_CB;
            pr1[1] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr1[1].Value = cbcv.Ma_CV;
            pr1[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[2].Value = cbcv.Ma_DV;
            sqlHelper.Fill(check, "select Ma_CV, Ma_DV from CANBO_CHUCVU where Ma_CV=@Ma_CV and Ma_DV=@Ma_DV", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CANBO_CHUCVU (Ma_CB, Ma_CV, Ma_DV, QuyetDinh, NgayKy, FileQuyetDinh, TrangThaiCV) values (@Ma_CB, @Ma_CV, @Ma_DV, @QuyetDinh, @NgayKy, @FileQuyetDinh, 1)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;

        }

        public int updateCBCV(CANBO_CHUCVU cbcv)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbcv.Ma_CB;
            pr[1] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[1].Value = cbcv.Ma_CV;
            pr[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[2].Value = cbcv.Ma_DV;

            pr[3] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[3].Value = cbcv.QuyetDinh;
            pr[4] = new SqlParameter("@NgayKy", SqlDbType.Date);
            pr[4].Value = cbcv.NgayKy;
            pr[5] = new SqlParameter("@FileQuyetDinh", SqlDbType.Binary);
            pr[5].Value = cbcv.FileQuyetDinh;
            //pr[6] = new SqlParameter("@KiemNhiem", SqlDbType.Bit);
            //pr[6].Value = cbcv.KiemNhiem;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CANBO_CHUCVU set QuyetDinh=@QuyetDinh, NgayKy=@NgayKy, FileQuyetDinh=@FileQuyetDinh where Ma_CB=@Ma_CB and Ma_DV=@Ma_DV and Ma_CV=@Ma_CV", pr);
            return kq;
        }

        public void deleteCBCV(string Ma_CB, string Ma_CV, string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            pr[1] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[1].Value = Ma_CV;
            pr[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[2].Value = Ma_DV;

            sqlHelper.ExecuteNonQuery("update CANBO_CHUCVU set TrangThaiCV=0 where Ma_CB=@Ma_CB and Ma_CV=@Ma_CV and Ma_DV=@Ma_DV", pr);

        }
        
        //-------------CAN BO KHEN THUONG------------------
        
        public List<CANBO_KHENTHUONG> getCBKT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from CANBO_KHENTHUONG");

            List<CANBO_KHENTHUONG> listCBKT = new List<CANBO_KHENTHUONG>();

            foreach(DataRow dr in dt.Rows)
            {
                CANBO_KHENTHUONG cbkt = new CANBO_KHENTHUONG();

                cbkt.NoiDung = dr[0].ToString();
                cbkt.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkt.QuyetDinh = dr[2].ToString();
                cbkt.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkt.Ma_CB = dr[4].ToString();
                cbkt.Ma_KT = Int32.Parse(dr[5].ToString());

                listCBKT.Add(cbkt);
            }
            return listCBKT;
        }

        public int addCBKT(CANBO_KHENTHUONG cbkt)
        {
            SqlParameter[] pr = new SqlParameter[6];

            pr[0] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[0].Value = cbkt.NoiDung;
            pr[1] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[1].Value = cbkt.QuyetDinh;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = cbkt.NgayKy;
            pr[3] = new SqlParameter("@FileQuyetDinh", SqlDbType.Image);
            pr[3].Value = cbkt.FileQuyetDinh;
            pr[4] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[4].Value = cbkt.Ma_CB;
            pr[5] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[5].Value = cbkt.Ma_KT;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[2];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cbkt.Ma_CB;
            pr1[1] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr1[1].Value = cbkt.Ma_KT;
            sqlHelper.Fill(check,"select Ma_CB, Ma_KT from CANBO_KHENTHUONG where Ma_CB=@Ma_CB and Ma_KT=@Ma_KT",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CANBO_KHENTHUONG (Ma_CB, Ma_KT, NoiDung, QuyetDinh, NgayKy, FileQuyetDinh) values (@Ma_CB, @Ma_KT, @NoiDung, @QuyetDinh, @NgayKy, @FileQuyetDinh)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateCBKT(CANBO_KHENTHUONG cbkt)
        {
            SqlParameter[] pr = new SqlParameter[6];

            pr[0] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[0].Value = cbkt.NoiDung;
            pr[1] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[1].Value = cbkt.QuyetDinh;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = cbkt.NgayKy;
            pr[3] = new SqlParameter("@FileQuyetDinh", SqlDbType.Image);
            pr[3].Value = cbkt.FileQuyetDinh;
            pr[4] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[4].Value = cbkt.Ma_CB;
            pr[5] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[5].Value = cbkt.Ma_KT;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CANBO_KHENTHUONG set NoiDung=@NoiDung, QuyetDinh=@QuyetDinh, NgayKy=@NgayKy, FileQuyetDinh=@FileQuyetDinh where Ma_CB=@Ma_CB and Ma_KT=@Ma_KT", pr);

            return kq;
        }

        public void deleteCBKT(int Ma_KT, string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            pr[1] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[1].Value = Ma_KT;

            sqlHelper.ExecuteNonQuery("delete from CANBO_KHENTHUONG where Ma_CB=@Ma_CB and Ma_KT=@Ma_KT",pr);
            }

        public List<CANBO_KYLUAT> getCBKL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from CANBO_KYLUAT");

            List<CANBO_KYLUAT> listCBKL = new List<CANBO_KYLUAT>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_KYLUAT cbkl = new CANBO_KYLUAT();

                cbkl.NoiDung = dr[0].ToString();
                cbkl.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkl.QuyetDinh = dr[2].ToString();
                cbkl.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkl.Ma_CB = dr[4].ToString();
                cbkl.Ma_KL = Int32.Parse(dr[5].ToString());

                listCBKL.Add(cbkl);
            }
            return listCBKL;
        }

        public int addCBKL(CANBO_KYLUAT cbkl)
        {
            SqlParameter[] pr = new SqlParameter[6];

            pr[0] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[0].Value = cbkl.NoiDung;
            pr[1] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[1].Value = cbkl.QuyetDinh;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = cbkl.NgayKy;
            pr[3] = new SqlParameter("@FileQuyetDinh", SqlDbType.Image);
            pr[3].Value = cbkl.FileQuyetDinh;
            pr[4] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[4].Value = cbkl.Ma_CB;
            pr[5] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[5].Value = cbkl.Ma_KL;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[2];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cbkl.Ma_CB;
            pr1[1] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr1[1].Value = cbkl.Ma_KL;
            sqlHelper.Fill(check, "select Ma_CB, Ma_KL from CANBO_KYLUAT where Ma_CB=@Ma_CB and Ma_KL=@Ma_KL", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CANBO_KYLUAT (Ma_CB, Ma_KL, NoiDung, QuyetDinh, NgayKy, FileQuyetDinh) values (@Ma_CB, @Ma_KL, @NoiDung, @QuyetDinh, @NgayKy, @FileQuyetDinh)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateCBKL(CANBO_KYLUAT cbkl)
        {
            SqlParameter[] pr = new SqlParameter[6];

            pr[0] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[0].Value = cbkl.NoiDung;
            pr[1] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[1].Value = cbkl.QuyetDinh;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = cbkl.NgayKy;
            pr[3] = new SqlParameter("@FileQuyetDinh", SqlDbType.Image);
            pr[3].Value = cbkl.FileQuyetDinh;
            pr[4] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[4].Value = cbkl.Ma_CB;
            pr[5] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[5].Value = cbkl.Ma_KL;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CANBO_KYLUAT set NoiDung=@NoiDung, QuyetDinh=@QuyetDinh, NgayKy=@NgayKy, FileQuyetDinh=@FileQuyetDinh where Ma_CB=@Ma_CB and Ma_KL=@Ma_KL", pr);
            return kq;
        }

        public void deleteCBKL(int Ma_KL, string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            pr[1] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[1].Value = Ma_KL;

            sqlHelper.ExecuteNonQuery("delete from CANBO_KYLUAT where Ma_CB=@Ma_CB and Ma_KL=@Ma_KL", pr);
        }

        public List<HOSO_DANG> getHSD()
        {
            throw new NotImplementedException();
        }

        public int addHSD(HOSO_DANG hsd)
        {
            throw new NotImplementedException();
        }

        public int updateHSD(HOSO_DANG hsd)
        {
            throw new NotImplementedException();
        }

        public void deleteHSD(string Ma_HSD)
        {
            throw new NotImplementedException();
        }

        public List<CHITIET_SUCKHOE> getCTSK()
        {
            throw new NotImplementedException();
        }

        public int addCTSK(CHITIET_SUCKHOE ctsk)
        {
            throw new NotImplementedException();
        }

        public int updateCTSK(CHITIET_SUCKHOE ctsk)
        {
            throw new NotImplementedException();
        }

        public void deleteCTSK(string Stt_SucKhoe)
        {
            throw new NotImplementedException();
        }

        public List<CHITIET_NHAPNGU> getCTNN()
        {
            throw new NotImplementedException();
        }

        public int addCTNN(CHITIET_NHAPNGU ctnn)
        {
            throw new NotImplementedException();
        }

        public int updateCTNN(CHITIET_NHAPNGU ctnn)
        {
            throw new NotImplementedException();
        }

        public void deleteCTNN(string Stt_NhapNgu)
        {
            throw new NotImplementedException();
        }

        public List<PHU_CAP> getPC()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from PHU_CAP");
            List<PHU_CAP> listPC = new List<PHU_CAP>();

            foreach (DataRow dr in dt.Rows)
            {
                PHU_CAP pc = new PHU_CAP();
                pc.Ma_PC = dr[0].ToString();
                pc.DienGiai = dr[1].ToString();
                pc.HeSoPC = float.Parse(dr[2].ToString());
                pc.DVT = dr[3].ToString();
                listPC.Add(pc);
            }
            return listPC;
        }

        public int addPC(PHU_CAP pc)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_PC",SqlDbType.Char);
            pr[0].Value = pc.Ma_PC;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = pc.DienGiai;
            pr[2] = new SqlParameter("@HeSoPC",SqlDbType.Float);
            pr[2].Value = pc.HeSoPC;
            pr[3] = new SqlParameter("@DVT", SqlDbType.Char);
            pr[3].Value = pc.DVT;
            
            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_PC", SqlDbType.Char);
            pr1[0].Value = pc.Ma_PC;
            sqlHelper.Fill(check, "select Ma_PC from PHU_CAP where Ma_PC=@Ma_PC", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert PHU_CAP (Ma_PC, DienGiai, HeSoPC, DVT) values (@Ma_PC, @DienGiai, @HeSoPC, @DVT)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updatePC(PHU_CAP pc)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@Ma_PC", SqlDbType.Char);
            pr[0].Value = pc.Ma_PC;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = pc.DienGiai;
            pr[2] = new SqlParameter("@HeSoPC", SqlDbType.Float);
            pr[2].Value = pc.HeSoPC;
            pr[3] = new SqlParameter("@DVT", SqlDbType.Char);
            pr[3].Value = pc.DVT;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update PHU_CAP set DienGiai=@DienGiai, HeSoPC=@HeSoPC, DVT=@DVT where Ma_PC=@Ma_PC",pr);
            return kq;
        }

        public void deletePC(string Ma_PC)
        {
            throw new NotImplementedException();
        }

        public List<CANBO_PHUCAP> getCBPC()
        {
            throw new NotImplementedException();
        }

        public int addCBPC(CANBO_PHUCAP cbpc)
        {
            throw new NotImplementedException();
        }

        public int updateCBPC(CANBO_PHUCAP cbpc)
        {
            throw new NotImplementedException();
        }

        public void deleteCBPC(string Ma_PC, string Ma_CB)
        {
            throw new NotImplementedException();
        }

        public List<DANH_HIEU> getDH()
        {
            throw new NotImplementedException();
        }

        public int addDH(DANH_HIEU dh)
        {
            throw new NotImplementedException();
        }

        public int updateDH(DANH_HIEU dh)
        {
            throw new NotImplementedException();
        }

        public void deleteDH(string Ma_DH)
        {
            throw new NotImplementedException();
        }

        public List<CANBO_DANHHIEU> getCBDH()
        {
            throw new NotImplementedException();
        }

        public int addCBDH(CANBO_DANHHIEU cbdh)
        {
            throw new NotImplementedException();
        }

        public int updateCBDH(CANBO_DANHHIEU cbdh)
        {
            throw new NotImplementedException();
        }

        public void deleteCBDH(string Ma_DH, string Ma_CB)
        {
            throw new NotImplementedException();
        }


        public List<BAC_LUONG> getMaBL()
        {
            DataTable dt = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            sqlHelper.Fill(dt, "select Ma_BL from BAC_LUONG");
            List<BAC_LUONG> listBL = new List<BAC_LUONG>();
            foreach (DataRow dr in dt.Rows)
            {
                BAC_LUONG bl = new BAC_LUONG();
                bl.Ma_BL = Int32.Parse(dr[0].ToString());
                listBL.Add(bl);
            }
            return listBL;
        }


        public List<CANBO_NGACH_LUONG> getCBNL()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from CANBO_NGACH_LUONG");
            List<CANBO_NGACH_LUONG> listCBNL = new List<CANBO_NGACH_LUONG>();
            foreach (DataRow dr in dt.Rows)
            {
                CANBO_NGACH_LUONG cbnl = new CANBO_NGACH_LUONG();
                cbnl.Ma_CB = dr[0].ToString();
                cbnl.Ma_Ngach = dr[1].ToString();
                cbnl.Ma_BL = Int32.Parse(dr[2].ToString());
                cbnl.QuyetDinh = dr[3].ToString();
                cbnl.NoiDung = dr[4].ToString();
                cbnl.NgayKy = DateTime.Parse(dr[5].ToString());    
                cbnl.NgayHieuLuc = DateTime.Parse(dr[6].ToString());
                cbnl.FileQuyetDinh = (byte[])dr[7];
                listCBNL.Add(cbnl);
            }
            return listCBNL;
        }

        public int addCBNL(CANBO_NGACH_LUONG cbnl)
        {
            SqlParameter[] pr=new SqlParameter[8];

            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbnl.Ma_CB;
            pr[1] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[1].Value = cbnl.Ma_Ngach;
            pr[2] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[2].Value = cbnl.Ma_BL;
            pr[3] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[3].Value = cbnl.QuyetDinh;
            pr[4] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[4].Value = cbnl.NoiDung;
            pr[5] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[5].Value = cbnl.NgayKy;
            pr[6] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr[6].Value = cbnl.NgayHieuLuc;
            pr[7] = new SqlParameter("@FileQuyetDinh", SqlDbType.Binary);
            pr[7].Value = cbnl.FileQuyetDinh;
                      
            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[3];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cbnl.Ma_CB;
            pr1[1] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr1[1].Value = cbnl.Ma_Ngach;
            pr1[2] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr1[2].Value = cbnl.Ma_BL;

            sqlHelper.Fill(check, "select Ma_CB, Ma_Ngach, Ma_BL from CANBO_NGACH_LUONG where Ma_CB=@Ma_CB and Ma_Ngach=@Ma_Ngach and Ma_BL=@Ma_BL", pr1);

            int kq;
            if (check.Rows.Count==0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CANBO_NGACH_LUONG (Ma_CB, Ma_Ngach, Ma_BL, QuyetDinh, NoiDung, NgayKy, NgayHieuLuc, FileQuyetDinh) values (@Ma_CB, @Ma_Ngach, @Ma_BL, @QuyetDinh, @NoiDung, @NgayKy, @NgayHieuLuc, @FileQuyetDinh)", pr);
            }
            else
            {
               kq = -1;
            }
            return kq;
        }

        public int updateCBNL(CANBO_NGACH_LUONG cbnl)
        {
            SqlParameter[] pr = new SqlParameter[8];

            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbnl.Ma_CB;
            pr[1] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[1].Value = cbnl.Ma_Ngach;
            pr[2] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[2].Value = cbnl.Ma_BL;
            pr[3] = new SqlParameter("@QuyetDinh", SqlDbType.Char);
            pr[3].Value = cbnl.QuyetDinh;
            pr[4] = new SqlParameter("@NoiDung", SqlDbType.NVarChar);
            pr[4].Value = cbnl.NoiDung;
            pr[5] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[5].Value = cbnl.NgayKy;
            pr[6] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr[6].Value = cbnl.NgayHieuLuc;
            pr[7] = new SqlParameter("@FileQuyetDinh", SqlDbType.Binary);
            pr[7].Value = cbnl.FileQuyetDinh;
         
            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CANBO_NGACH_LUONG set QuyetDinh=@QuyetDinh, NoiDung=@NoiDung, NgayKy=@NgayKy, NgayHieuLuc=@NgayHieuLuc, FileQuyetDinh=@FileQuyetDinh where Ma_CB=@Ma_CB and Ma_Ngach=@Ma_Ngach and Ma_BL=@Ma_BL", pr);
            return kq;
        }

        public void deleteCBNL(string Ma_CB, string Ma_Ngach, string Ma_BL)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            pr[1] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[1].Value = Ma_Ngach;
            pr[2] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[2].Value = Ma_BL;
            
            sqlHelper.ExecuteNonQuery("delete from CANBO_NGACH_LUONG where Ma_CB=@Ma_CB and Ma_Ngach=@Ma_Ngach and Ma_BL=@Ma_BL",pr);
        }



        //-----------------------------------CAN BO---------------------------------


        public List<CAN_BO> getCB()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from CAN_BO");
            List<CAN_BO> listCB = new List<CAN_BO>();
            foreach(DataRow dr in dt.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.Ma_CB = dr[0].ToString();
                cb.HoTen = dr[1].ToString();
                cb.TenKhac = dr[2].ToString();
                cb.NamSinh = DateTime.Parse(dr[3].ToString());
                cb.GioiTinh = dr[4].ToString();
                cb.NoiSinh = dr[5].ToString();
                cb.QueQuan = dr[6].ToString();
                cb.HoKhau = dr[7].ToString();
                cb.Cho_O = dr[8].ToString();
                cb.NgheNghiep = dr[9].ToString();
                cb.NgayTuyenDung = DateTime.Parse(dr[10].ToString());
                cb.DangVien = bool.Parse(dr[11].ToString());
                cb.NhapNgu = bool.Parse(dr[12].ToString());
                cb.SucKhoe = dr[13].ToString();
                cb.ThuongBinh = dr[14].ToString();
                cb.SoCMND = dr[15].ToString();
                cb.NgayCap = DateTime.Parse(dr[16].ToString());
                cb.SoBHXH = dr[17].ToString();
                cb.HinhAnh = Encoding.ASCII.GetBytes(dr[18].ToString());
                cb.NoiCap = dr[19].ToString();
                cb.Ma_DT = Int32.Parse(dr[20].ToString());
                cb.Ma_TG = Int32.Parse(dr[21].ToString());
                cb.Stt_GD_CS = Int32.Parse(dr[22].ToString());
                cb.Ma_DV = dr[23].ToString();

                listCB.Add(cb);

            }
            return listCB;
        }

        

        public int addCB(CAN_BO cb)
        {
            SqlParameter[] pr = new SqlParameter[24];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cb.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = cb.HoTen;
            pr[2] = new SqlParameter("@TenKhac", SqlDbType.NVarChar);
            pr[2].Value = cb.TenKhac;
            pr[3] = new SqlParameter("@NamSinh", SqlDbType.DateTime);
            pr[3].Value = cb.NamSinh;
            pr[4] = new SqlParameter("@GioiTinh", SqlDbType.NVarChar);
            pr[4].Value = cb.GioiTinh;
            pr[5] = new SqlParameter("@NoiSinh", SqlDbType.NVarChar);
            pr[5].Value = cb.NoiSinh;
            pr[6] = new SqlParameter("@QueQuan", SqlDbType.NVarChar);
            pr[6].Value = cb.QueQuan;
            pr[7] = new SqlParameter("@HoKhau", SqlDbType.NVarChar);
            pr[7].Value = cb.HoKhau;
            pr[8] = new SqlParameter("@Cho_O", SqlDbType.NVarChar);
            pr[8].Value = cb.Cho_O;
            pr[9] = new SqlParameter("@NgheNghiep", SqlDbType.NVarChar);
            pr[9].Value = cb.NgheNghiep;
            pr[10] = new SqlParameter("@NgayTuyenDung", SqlDbType.DateTime);
            pr[10].Value = cb.NgayTuyenDung;
            pr[11] = new SqlParameter("@DangVien", SqlDbType.Bit);
            pr[11].Value = cb.DangVien;
            pr[12] = new SqlParameter("@NhapNgu", SqlDbType.Bit);
            pr[12].Value = cb.NhapNgu;
            pr[13] = new SqlParameter("@SucKhoe", SqlDbType.NVarChar);
            pr[13].Value = cb.SucKhoe;
            pr[14] = new SqlParameter("@ThuongBinh", SqlDbType.NVarChar);
            pr[14].Value = cb.ThuongBinh;
            pr[15] = new SqlParameter("@SoCMND", SqlDbType.Char);
            pr[15].Value = cb.SoCMND;
            pr[16] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[16].Value = cb.NgayCap;
            pr[17] = new SqlParameter("@SoBHXH", SqlDbType.Char);
            pr[17].Value = cb.SoBHXH;
            pr[18] = new SqlParameter("@HinhAnh", SqlDbType.Image);
            pr[18].Value = cb.HinhAnh;
            pr[19] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[19].Value = cb.NoiCap;
            pr[20] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[20].Value = cb.Ma_DT;
            pr[21] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[21].Value = cb.Ma_TG;
            pr[22] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[22].Value = cb.Stt_GD_CS;
            pr[23] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[23].Value = cb.Ma_DV;


            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cb.Ma_CB;
            sqlHelper.Fill(check,"select Ma_CB from CAN_BO where Ma_CB=@Ma_CB",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CAN_BO (Ma_CB, HoTen, TenKhac, NamSinh, GioiTinh, NoiSinh, QueQuan, HoKhau, Cho_O, NgheNghiep, NgayTuyenDung, DangVien, NhapNgu, SucKhoe, ThuongBinh, SoCMND, NgayCap, SoBHXH, HinhAnh,  NoiCap, Ma_DT, Ma_TG, Stt_GD_CS, Ma_DV) values (@Ma_CB, @HoTen, @TenKhac, @NamSinh, @GioiTinh, @NoiSinh, @QueQuan, @HoKhau, @Cho_O, @NgheNghiep, @NgayTuyenDung, @DangVien, @NhapNgu, @SucKhoe, @ThuongBinh, @SoCMND, @NgayCap, @SoBHXH, @HinhAnh, @NoiCap,  @Ma_DT, @Ma_TG, @Stt_GD_CS, @Ma_DV)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;

        }


        public int updateCB(CAN_BO cb)
        {
            SqlParameter[] pr = new SqlParameter[24];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cb.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = cb.HoTen;
            pr[2] = new SqlParameter("@TenKhac", SqlDbType.NVarChar);
            pr[2].Value = cb.TenKhac;
            pr[3] = new SqlParameter("@NamSinh", SqlDbType.DateTime);
            pr[3].Value = cb.NamSinh;
            pr[4] = new SqlParameter("@GioiTinh", SqlDbType.NVarChar);
            pr[4].Value = cb.GioiTinh;
            pr[5] = new SqlParameter("@NoiSinh", SqlDbType.NVarChar);
            pr[5].Value = cb.NoiSinh;
            pr[6] = new SqlParameter("@QueQuan", SqlDbType.NVarChar);
            pr[6].Value = cb.QueQuan;
            pr[7] = new SqlParameter("@HoKhau", SqlDbType.NVarChar);
            pr[7].Value = cb.HoKhau;
            pr[8] = new SqlParameter("@Cho_O", SqlDbType.NVarChar);
            pr[8].Value = cb.Cho_O;
            pr[9] = new SqlParameter("@NgheNghiep", SqlDbType.NVarChar);
            pr[9].Value = cb.NgheNghiep;
            pr[10] = new SqlParameter("@NgayTuyenDung", SqlDbType.DateTime);
            pr[10].Value = cb.NgayTuyenDung;
            pr[11] = new SqlParameter("@DangVien", SqlDbType.Bit);
            pr[11].Value = cb.DangVien;
            pr[12] = new SqlParameter("@NhapNgu", SqlDbType.Bit);
            pr[12].Value = cb.NhapNgu;
            pr[13] = new SqlParameter("@SucKhoe", SqlDbType.NVarChar);
            pr[13].Value = cb.SucKhoe;
            pr[14] = new SqlParameter("@ThuongBinh", SqlDbType.NVarChar);
            pr[14].Value = cb.ThuongBinh;
            pr[15] = new SqlParameter("@SoCMND", SqlDbType.Char);
            pr[15].Value = cb.SoCMND;
            pr[16] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[16].Value = cb.NgayCap;
            pr[17] = new SqlParameter("@SoBHXH", SqlDbType.Char);
            pr[17].Value = cb.SoBHXH;
            pr[18] = new SqlParameter("@HinhAnh", SqlDbType.Image);
            pr[18].Value = cb.HinhAnh;
            pr[19] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[19].Value = cb.NoiCap;
            pr[20] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[20].Value = cb.Ma_DT;
            pr[21] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[21].Value = cb.Ma_TG;
            pr[22] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[22].Value = cb.Stt_GD_CS;
            pr[23] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[23].Value = cb.Ma_DV;


            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CAN_BO set HoTen=@HoTen, TenKhac=@TenKhac, NamSinh=@NamSinh, GioiTinh=@GioiTinh, NoiSinh=@NoiSinh, QueQuan=@QueQuan, HoKhau=@HoKhau, Cho_O=@Cho_O, NgheNghiep=@NgheNghiep, NgayTuyenDung=@NgayTuyenDung, DangVien=@DangVien, NhapNgu=@NhapNgu, SucKhoe=@SucKhoe, ThuongBinh=@ThuongBinh, SoCMND=@SoCMND, NgayCap=@NgayCap, SoBHXH=@SoBHXH, HinhAnh=@HinhAnh, NoiCap=@NoiCap, Ma_DT=@Ma_DT, Ma_TG=@Ma_TG, Stt_GD_CS=@Stt_GD_CS, Ma_DV=@Ma_DV where Ma_CB=@Ma_CB", pr);
            return kq;
        }

        public int deleteCB(string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;

            SqlParameter[]  pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = Ma_CB;

            DataTable check = new DataTable();
            sqlHelper.Fill(check, "select cb.Ma_CB from CAN_BO cb, CANBO_NGACH_LUONG cbnl where cb.Ma_CB=cbnl.Ma_CB and cb.Ma_CB=@Ma_CB", pr);
            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("delete from CAN_BO where Ma_CB=@Ma_CB", pr1);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }





        public List<CANBO_NGACH_LUONG> getCBNLtheoDV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            DataTable dt = new DataTable();
            List<CANBO_NGACH_LUONG> ListCBNLtheoDV = new List<CANBO_NGACH_LUONG>();
            sqlHelper.Fill(dt, "select cbnl.QuyetDinh, cbnl.NgayKy, cbnl.NoiDung, cbnl.NgayHieuLuc, cbnl.FileQuyetDinh, cbnl.Ma_CB, cbnl.Ma_Ngach, cbnl.Ma_BL, cbnl.Ma_HS_Luong  from CANBO_NGACH_LUONG cbnl, CAN_BO cb, DANHMUC_DONVI dmdv where cbnl.Ma_CB=cb.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV", pr);
            foreach (DataRow dr in dt.Rows)
            {
                CANBO_NGACH_LUONG cbnl = new CANBO_NGACH_LUONG();
                cbnl.QuyetDinh = dr[0].ToString();
                cbnl.NgayKy = DateTime.Parse(dr[1].ToString());
                cbnl.NoiDung = dr[2].ToString();
                cbnl.NgayHieuLuc = DateTime.Parse(dr[3].ToString());
                string c = dr[4].ToString();
                cbnl.FileQuyetDinh = Encoding.ASCII.GetBytes(c);
                cbnl.Ma_CB = dr[5].ToString();
                cbnl.Ma_Ngach = dr[6].ToString();
                cbnl.Ma_BL = Int32.Parse(dr[7].ToString());
               
                ListCBNLtheoDV.Add(cbnl);
            }
            return ListCBNLtheoDV;
        }


        public List<LOAI_NGACH> getLNG()
        {
            DataTable dt = new DataTable();
            List<LOAI_NGACH> listLNG = new List<LOAI_NGACH>();
            sqlHelper.Fill(dt, "select *from LOAI_NGACH");
            foreach (DataRow dr in dt.Rows)
            {
                LOAI_NGACH lng = new LOAI_NGACH();
                lng.Ma_Loai_Ngach = dr[0].ToString();
                lng.TenLoaiNgach = dr[1].ToString();
                listLNG.Add(lng);
            }
            return listLNG;
        }

        public int addLN(LOAI_NGACH lng)
        {
            throw new NotImplementedException();
        }

        public int updateLNG(LOAI_NGACH lng)
        {
            throw new NotImplementedException();
        }

        public void deleteLNG(string Ma_Loai_Ngach)
        {
            throw new NotImplementedException();
        }


        public List<CAN_BO> getCBtheoDV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen from CAN_BO cb, DANHMUC_DONVI dmdv where cb.Ma_DV=dmdv.Ma_DV and cb.Ma_DV=@Ma_DV", pr);
            List<CAN_BO> listCBtheoDV = new List<CAN_BO>();

            foreach (DataRow dr in dt.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.Ma_CB = dr[0].ToString();
                cb.HoTen = dr[1].ToString();
                listCBtheoDV.Add(cb);
            }
            return listCBtheoDV;
        }


        public List<HESO_LUONG> getBLHSLtheoLNG(string Ma_Loai_Ngach)
        {
            SqlParameter[] pr=new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[0].Value = Ma_Loai_Ngach;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select hsl.Ma_HS_Luong, hsl.Ma_BL, bl.TenBL from HESO_LUONG hsl, BAC_LUONG bl where hsl.Ma_BL=bl.Ma_BL and Ma_Loai_Ngach=@Ma_Loai_Ngach", pr);

            List<HESO_LUONG> listBLHSLtheoLNG = new List<HESO_LUONG>();
            foreach (DataRow dr in dt.Rows)
            {
                HESO_LUONG hsl = new HESO_LUONG();
   
                hsl.Ma_HS_Luong = float.Parse(dr[0].ToString());
                hsl.Ma_BL = Int32.Parse(dr[1].ToString());

                listBLHSLtheoLNG.Add(hsl);
            }
            return listBLHSLtheoLNG;
        }


        public List<NGACH> getMaLoaitheoMaNgach(string Ma_Ngach)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_Ngach", SqlDbType.Char);
            pr[0].Value = Ma_Ngach;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select Ma_Loai_Ngach from NGACH where Ma_Ngach=@Ma_Ngach", pr);
            List<NGACH> listMaLoaitheoMaNgach = new List<NGACH>();

            foreach (DataRow dr in dt.Rows)
            {
                NGACH ng = new NGACH();
                ng.Ma_Loai_Ngach = dr[0].ToString();
                listMaLoaitheoMaNgach.Add(ng);
            }
            return listMaLoaitheoMaNgach;
        }


        public List<HESO_LUONG> getHSLtheoBL(string Ma_Loai_Ngach, string Ma_BL)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_Loai_Ngach", SqlDbType.Char);
            pr[0].Value = Ma_Loai_Ngach;
            pr[1] = new SqlParameter("@Ma_BL", SqlDbType.Char);
            pr[1].Value = Ma_BL;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select Ma_HS_Luong from HESO_LUONG where Ma_Loai_Ngach=@Ma_Loai_Ngach and Ma_BL=@Ma_BL",pr);

            List<HESO_LUONG> listHSLtheoBL = new List<HESO_LUONG>();

            foreach (DataRow dr in dt.Rows)
            {
                HESO_LUONG hsl = new HESO_LUONG();
                hsl.Ma_HS_Luong = float.Parse(dr[0].ToString());
                listHSLtheoBL.Add(hsl);
            }
            return listHSLtheoBL;
        }


        public List<DANHMUC_DONVI> getTenDVtheoMaCB(string Ma_CB)
        {
            DataTable dt = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;

            List<DANHMUC_DONVI> listTenDVtheoMaCB = new List<DANHMUC_DONVI>();
            sqlHelper.Fill(dt, "select dmdv.Ma_DV from DANHMUC_DONVI dmdv, CAN_BO cb where dmdv.Ma_DV=cb.Ma_DV and cb.Ma_CB=@Ma_CB", pr);

            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dmdv = new DANHMUC_DONVI();
                dmdv.Ma_DV = dr[0].ToString();

                listTenDVtheoMaCB.Add(dmdv);
            }
            return listTenDVtheoMaCB;
        }


        public List<CANBO_KHENTHUONG> getCBKTtheoDV(string Ma_DV_HC)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = Ma_DV_HC;
            DataTable dt = new DataTable();
            List<CANBO_KHENTHUONG> listCBKTtheoDV = new List<CANBO_KHENTHUONG>();
            sqlHelper.Fill(dt, "select cbkt.NoiDung, cbkt.NgayKy, cbkt.QuyetDinh, cbkt.FileQuyetDinh, cbkt.Ma_CB, cbkt.Ma_KT from CANBO_KHENTHUONG cbkt, CAN_BO cb, DANHMUC_DONVI dvhc where cbkt.Ma_CB=cb.Ma_CB and cb.Ma_DV=dvhc.Ma_DV and dvhc.Ma_DV=@Ma_DV_HC", pr);
            foreach (DataRow dr in dt.Rows)
            {
                CANBO_KHENTHUONG cbkt = new CANBO_KHENTHUONG();
                cbkt.NoiDung = dr[0].ToString();
                cbkt.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkt.QuyetDinh = dr[2].ToString();
                cbkt.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkt.Ma_CB = dr[4].ToString();
                cbkt.Ma_KT = Int32.Parse(dr[5].ToString());

                listCBKTtheoDV.Add(cbkt);
            }
            return listCBKTtheoDV;
        }

        public List<CANBO_KYLUAT> getCBKLtheoDV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cbkl.NoiDung, cbkl.NgayKy, cbkl.QuyetDinh, cbkl.FileQuyetDinh, cbkl.Ma_CB, cbkl.Ma_KL from CANBO_KYLUAT cbkl, CAN_BO cb, DANHMUC_DONVI dvhc where cbkl.Ma_CB=cb.Ma_CB and cb.Ma_DV=dvhc.Ma_DV and dvhc.Ma_DV=@Ma_DV", pr);
            List<CANBO_KYLUAT> listCBKLtheoDV = new List<CANBO_KYLUAT>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_KYLUAT cbkl = new CANBO_KYLUAT();
                cbkl.NoiDung = dr[0].ToString();
                cbkl.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkl.QuyetDinh = dr[2].ToString();
                cbkl.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkl.Ma_CB = dr[4].ToString();
                cbkl.Ma_KL = Int32.Parse(dr[5].ToString());

                listCBKLtheoDV.Add(cbkl);
            }
            return listCBKLtheoDV;
        }


        public List<CANBO_KHENTHUONG> getCBKTtheoKT(int Ma_KT)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from CANBO_KHENTHUONG where Ma_KT=@Ma_KT",pr);

            List<CANBO_KHENTHUONG> listCBKTtheoKT = new List<CANBO_KHENTHUONG>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_KHENTHUONG cbkt = new CANBO_KHENTHUONG();
                cbkt.NoiDung = dr[0].ToString();
                cbkt.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkt.QuyetDinh = dr[2].ToString();
                cbkt.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkt.Ma_CB = dr[4].ToString();
                cbkt.Ma_KT = Int32.Parse(dr[5].ToString());

                listCBKTtheoKT.Add(cbkt);
            }
            return listCBKTtheoKT;
        }


        public List<CANBO_KYLUAT> getCBKLtheoKL(int Ma_KL)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from CANBO_KYLUAT where Ma_KL=@Ma_KL", pr);

            List<CANBO_KYLUAT> listCBKTtheoKL = new List<CANBO_KYLUAT>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_KYLUAT cbkl = new CANBO_KYLUAT();
                cbkl.NoiDung = dr[0].ToString();
                cbkl.NgayKy = DateTime.Parse(dr[1].ToString());
                cbkl.QuyetDinh = dr[2].ToString();
                cbkl.FileQuyetDinh = Encoding.ASCII.GetBytes(dr[3].ToString());
                cbkl.Ma_CB = dr[4].ToString();
                cbkl.Ma_KL = Int32.Parse(dr[5].ToString());

                listCBKTtheoKL.Add(cbkl);
            }
            return listCBKTtheoKL;
        }


        public List<CAN_BO> getdsCBtheoDV(string Ma_DV)
        {
            SqlParameter[] pr=new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
                        
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from CAN_BO where Ma_DV=@Ma_DV", pr);
            List<CAN_BO> listdsCBtheoDV = new List<CAN_BO>();
            
            foreach(DataRow dr in dt.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.Ma_CB = dr[0].ToString();
                cb.HoTen = dr[1].ToString();
                cb.TenKhac = dr[2].ToString();
                cb.NamSinh = DateTime.Parse(dr[3].ToString());
                cb.GioiTinh = dr[4].ToString();
                cb.NoiSinh = dr[5].ToString();
                cb.QueQuan = dr[6].ToString();
                cb.HoKhau = dr[7].ToString();
                cb.Cho_O = dr[8].ToString();
                cb.NgheNghiep = dr[9].ToString();
                cb.NgayTuyenDung = DateTime.Parse(dr[10].ToString());
                cb.DangVien = bool.Parse(dr[11].ToString());
                cb.NhapNgu = bool.Parse(dr[12].ToString());
                cb.SucKhoe = dr[13].ToString();
                cb.ThuongBinh = dr[14].ToString();
                cb.SoCMND = dr[15].ToString();
                cb.NgayCap = DateTime.Parse(dr[16].ToString());
                cb.SoBHXH = dr[17].ToString();
                cb.HinhAnh = Encoding.ASCII.GetBytes(dr[18].ToString());
                cb.NoiCap = dr[19].ToString();
                cb.Ma_DT = Int32.Parse(dr[20].ToString());
                cb.Ma_TG = Int32.Parse(dr[21].ToString());
                cb.Stt_GD_CS = Int32.Parse(dr[22].ToString());
                cb.Ma_DV = dr[23].ToString();

                listdsCBtheoDV.Add(cb);
            }
            return listdsCBtheoDV;
        }


        public List<DANHMUC_DONVI> getDMDVtheoLoaiCV(string Ma_CV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr[0].Value = Ma_CV;

            List<DANHMUC_DONVI> listDMDVtheoLoaiCV = new List<DANHMUC_DONVI>();
            
            DataTable dt=new DataTable();
            sqlHelper.Fill(dt,"select dmdv.Ma_DV, dmdv.TenDV from CHUC_VU cv, LOAI_DV ldv, DANHMUC_DONVI dmdv where cv.Ma_Loai_DV=ldv.Ma_Loai_DV and ldv.Ma_Loai_DV=dmdv.Ma_Loai_DV	and cv.Ma_CV=@Ma_CV",pr);

            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dmdv = new DANHMUC_DONVI();
                dmdv.Ma_DV = dr[0].ToString();
                dmdv.TenDVTT = dr[1].ToString();

                listDMDVtheoLoaiCV.Add(dmdv);
            }
            return listDMDVtheoLoaiCV;
        }

        //------------------loai don vi-----------------------
        public List<LOAI_DV> getLoaiDV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from LOAI_DV");
            List<LOAI_DV> listLoaiDV = new List<LOAI_DV>();
            foreach(DataRow dr in dt.Rows)
            {
                LOAI_DV ldv = new LOAI_DV();
                ldv.Ma_Loai_DV = dr[0].ToString();
                ldv.TenLoaiDV = dr[1].ToString();

                listLoaiDV.Add(ldv);
            }
            return listLoaiDV;
        }


        public int addLoaiDV(LOAI_DV ldv)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[0].Value = ldv.Ma_Loai_DV;
            pr[1] = new SqlParameter("@TenLoaiDV",SqlDbType.NVarChar);
            pr[1].Value = ldv.TenLoaiDV;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr1[0].Value = ldv.Ma_Loai_DV;
            sqlHelper.Fill(check,"select Ma_Loai_DV from LOAI_DV where Ma_Loai_DV=@Ma_Loai_DV",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert LOAI_DV (Ma_Loai_DV, TenLoaiDV) values (@Ma_Loai_DV, @TenLoaiDV)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateLoaiDV(LOAI_DV ldv)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[0].Value = ldv.Ma_Loai_DV;
            pr[1] = new SqlParameter("@TenLoaiDV", SqlDbType.NVarChar);
            pr[1].Value = ldv.TenLoaiDV;
           
            int kq = sqlHelper.ExecuteNonQuery("update LOAI_DV set TenLoaiDV=@TenLoaiDV where Ma_Loai_DV=@Ma_Loai_DV", pr);
           
            return kq;
        }





        //-----------------THONG KE KHEN THUONG------------------------------

        public List<THONGKEKT> getThongKeKT(int Ma_KT, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=@Ma_KT and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);
            
            List<THONGKEKT> listThongKeKT = new List<THONGKEKT>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKT tk = new THONGKEKT();
                tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[1].ToString();
                tk.TenDV = dr[2].ToString();
                tk.NoiDung = dr[3].ToString();
                tk.NgayKy = DateTime.Parse(dr[4].ToString());

                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }


        public List<THONGKEKT> getAllThongke()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV");
            List<THONGKEKT> listThongKeKT = new List<THONGKEKT>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKT tk = new THONGKEKT();
                tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[1].ToString();
                tk.TenDV = dr[2].ToString();
                tk.NoiDung = dr[3].ToString();
                tk.NgayKy = DateTime.Parse(dr[4].ToString());

                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }


        public List<THONGKEKT> getThongketheoNam(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);
            
            List<THONGKEKT> listThongKeKT = new List<THONGKEKT>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKT tk = new THONGKEKT();
                tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[1].ToString();
                tk.TenDV = dr[2].ToString();
                tk.NoiDung = dr[3].ToString();
                tk.NgayKy = DateTime.Parse(dr[4].ToString());

                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }


        public int countSLKT(int Ma_KT, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=@Ma_KT and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);
            
            int c=dt.Rows.Count;

            return c;
        }


        public int countKTtheoNam(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countAllKT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV");

            int c = dt.Rows.Count;

            return c;
        }





        public int countLDTT(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=1 and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDCS(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=2 and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDCB(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=3 and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDTQ(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=4 and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }


        //---------------------THONG KE KY LUAT-------------------------




        public List<THONGKEKL> getThongKeKL(int Ma_KL, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=@Ma_KL and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);

            List<THONGKEKL> listThongKeKL = new List<THONGKEKL>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKL kl = new THONGKEKL();
                kl.HoTen = dr[0].ToString();
                kl.TenDV = dr[1].ToString();
                kl.NoiDung = dr[2].ToString();
                kl.NgayKy = DateTime.Parse(dr[3].ToString());

                listThongKeKL.Add(kl);
            }
            return listThongKeKL;
        }

        public List<THONGKEKL> getThongkeKLtheoNam(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);

            List<THONGKEKL> listThongKeKL = new List<THONGKEKL>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKL kl = new THONGKEKL();
                kl.HoTen = dr[0].ToString();
                kl.TenDV = dr[1].ToString();
                kl.NoiDung = dr[2].ToString();
                kl.NgayKy = DateTime.Parse(dr[3].ToString());

                listThongKeKL.Add(kl);
            }
            
            return listThongKeKL;
        }

        

        public int countSLKL(int Ma_KL, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=@Ma_KL and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countKLtheoNam(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and  (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countKhientrach(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=1 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countCanhcao(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=2 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countHabacluong(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=3 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countGiangchuc(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=4 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countCachchuc(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=5 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }

        public int countBuocthoiviec(DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[0].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=6 and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);
            int c = dt.Rows.Count;

            return c;
        }


        public List<SAPLENLUONG> getSLL()
        {
            //lay nhung can bo da len luong lan 2 ...bac CD. DH
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select hslht.Ma_CB, hslht.HoTen, hslht.Ten_Ngach, hslht.Ma_BL, hslht.NgayHieuLuc from HSL_HIENTAI hslht, NGACH ng, LOAI_NGACH lng where hslht.Ten_Ngach=ng.Ma_Ngach and ng.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and getdate()-hslht.NgayHieuLuc>900 and lng.Ma_Loai_Ngach!='CCVCB' and hslht.Ma_BL>=2");
            
            //lay nhung can bo moi len luong lan dau...tu ngay vao lam den 4 nam sau moi tang 1 bac luong....bac DH, CD
            DataTable dt1 = new DataTable();
            sqlHelper.Fill(dt1, "select hslht.Ma_CB, hslht.HoTen, hslht.Ten_Ngach, hslht.Ma_BL, hslht.NgayHieuLuc from HSL_HIENTAI hslht, NGACH ng, LOAI_NGACH lng where hslht.Ten_Ngach=ng.Ma_Ngach and ng.Ma_Loai_Ngach=lng.Ma_Loai_Ngach	and getdate()-hslht.NgayHieuLuc>1260 and lng.Ma_Loai_Ngach!='CCVCB'	and hslht.Ma_BL=1");

            DataTable dt2 = new DataTable();
            sqlHelper.Fill(dt2, "select hslht.Ma_CB, hslht.HoTen, hslht.Ten_Ngach, hslht.Ma_BL, hslht.NgayHieuLuc from HSL_HIENTAI hslht, NGACH ng, LOAI_NGACH lng where hslht.Ten_Ngach=ng.Ma_Ngach and ng.Ma_Loai_Ngach=lng.Ma_Loai_Ngach	and getdate()-hslht.NgayHieuLuc>540	and lng.Ma_Loai_Ngach='CCVCB' and hslht.Ma_BL>=2");

            DataTable dt3 = new DataTable();
            sqlHelper.Fill(dt3, "select hslht.Ma_CB, hslht.HoTen, hslht.Ten_Ngach, hslht.Ma_BL, hslht.NgayHieuLuc from HSL_HIENTAI hslht, NGACH ng, LOAI_NGACH lng	where hslht.Ten_Ngach=ng.Ma_Ngach and ng.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and getdate()-hslht.NgayHieuLuc>610 and lng.Ma_Loai_Ngach='CCVCB' and hslht.Ma_BL=1");

            List<SAPLENLUONG> listSLL = new List<SAPLENLUONG>();
            
                    foreach (DataRow dr in dt.Rows)
                    {
                        SAPLENLUONG sll = new SAPLENLUONG();
                        sll.Ma_CB = dr[0].ToString();
                        sll.HoTen = dr[1].ToString();
                        sll.Ma_Ngach = dr[2].ToString();
                        sll.Ma_BL = Int32.Parse(dr[3].ToString());
                        sll.NgayLLTRuoc = DateTime.Parse(dr[4].ToString());
                        listSLL.Add(sll);
                    }
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        SAPLENLUONG sll = new SAPLENLUONG();
                        sll.Ma_CB = dr1[0].ToString();
                        sll.HoTen = dr1[1].ToString();
                        sll.Ma_Ngach = dr1[2].ToString();
                        sll.Ma_BL = Int32.Parse(dr1[3].ToString());
                        sll.NgayLLTRuoc = DateTime.Parse(dr1[4].ToString());
                        listSLL.Add(sll);
                    }
                    foreach (DataRow dr2 in dt2.Rows)
                    {
                        SAPLENLUONG sll = new SAPLENLUONG();
                        sll.Ma_CB = dr2[0].ToString();
                        sll.HoTen = dr2[1].ToString();
                        sll.Ma_Ngach = dr2[2].ToString();
                        sll.Ma_BL = Int32.Parse(dr2[3].ToString());
                        sll.NgayLLTRuoc = DateTime.Parse(dr2[4].ToString());
                        listSLL.Add(sll);
                    }
                    foreach (DataRow dr3 in dt3.Rows)
                    {
                        SAPLENLUONG sll = new SAPLENLUONG();
                        sll.Ma_CB = dr3[0].ToString();
                        sll.HoTen = dr3[1].ToString();
                        sll.Ma_Ngach = dr3[2].ToString();
                        sll.Ma_BL = Int32.Parse(dr3[3].ToString());
                        sll.NgayLLTRuoc = DateTime.Parse(dr3[4].ToString());
                        listSLL.Add(sll);
                    }
             
            return listSLL;
        }


        public int countSSL()
        {
            DataTable dtcount = new DataTable();
            sqlHelper.Fill(dtcount, "select cb.HoTen, dmdv.TenDV, hsl.Ma_HS_Luong, lng.TenLoaiNgach from CAN_BO cb, CANBO_NGACH_LUONG cbnl, HESO_LUONG hsl,  LOAI_NGACH lng, DANHMUC_DONVI dmdv where cb.Ma_CB=cbnl.Ma_CB and cbnl.Ma_HS_Luong=hsl.Ma_HS_Luong and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and cb.Ma_DV=dmdv.Ma_DV and (GETDATE()-cbnl.NgayHieuLuc>540) and cbnl.Ma_BL>=1");
            int c = dtcount.Rows.Count;
            return c;
        }


        public List<RPKT> getSLKTtheoMaKT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select kt.DienGiai, count(cbkt.Ma_CB) from KHEN_THUONG kt, CANBO_KHENTHUONG cbkt where kt.Ma_KT=cbkt.Ma_KT group by kt.DienGiai");

            List<RPKT> listChitietKT = new List<RPKT>();
            foreach (DataRow dr in dt.Rows)
            {
                RPKT rpkt = new RPKT();
                rpkt.DienGiai = dr[0].ToString();
                rpkt.SoCB = Int32.Parse(dr[1].ToString());

                listChitietKT.Add(rpkt);
            }
            return listChitietKT;
        }


        public List<ReportLoaiKT> getReportLoaiKT(int Ma_KT, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy, kt.DienGiai from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=@Ma_KT and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            List<ReportLoaiKT> listThongKeKT = new List<ReportLoaiKT>();
            foreach (DataRow dr in dt.Rows)
            {
                ReportLoaiKT tk = new ReportLoaiKT();
                tk.HoTen = dr[0].ToString();
                tk.TenDV = dr[1].ToString();
                tk.NoiDung = dr[2].ToString();
                tk.NgayKy = DateTime.Parse(dr[3].ToString());
                tk.DienGiai = dr[4].ToString();
                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }

        public List<ReportLoaiKL> getReportLoaiKL(int Ma_KL, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy, kl.DienGiai from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=@Ma_KL and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);

            List<ReportLoaiKL> listThongKeKL = new List<ReportLoaiKL>();
            foreach (DataRow dr in dt.Rows)
            {
                ReportLoaiKL kl = new ReportLoaiKL();
                kl.HoTen = dr[0].ToString();
                kl.TenDV = dr[1].ToString();
                kl.NoiDung = dr[2].ToString();
                kl.NgayKy = DateTime.Parse(dr[3].ToString());
                kl.DienGiai = dr[4].ToString();

                listThongKeKL.Add(kl);
            }
            return listThongKeKL;
        }


        public List<SAPLENLUONG> getSLLtheoThang(DateTime NgayHieuLuc)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr[0].Value = NgayHieuLuc;

            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr1[0].Value = NgayHieuLuc;

            SqlParameter[] pr2 = new SqlParameter[1];
            pr2[0] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr2[0].Value = NgayHieuLuc;

            //SqlParameter[] pr3 = new SqlParameter[1];
            //pr3[0] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            //pr3[0].Value = NgayHieuLuc;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select hslht.Ma_CB, hslht.HoTen, dmdv.TenDV, hslht.HSL,lng.TenLoaiNgach, hslht.NgayHieuLuc from HSL_HIENTAI hslht, HESO_LUONG hsl,  LOAI_NGACH lng, CAN_BO cb, DANHMUC_DONVI dmdv, CANBO_NGACH_LUONG cbnl where cb.Ma_DV=dmdv.Ma_DV and hsl.Ma_HS_Luong=cbnl.Ma_HS_Luong and cbnl.Ma_CB=cb.Ma_CB and  hslht.HSL=hsl.Ma_HS_Luong	and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach	and lng.Ma_Loai_Ngach!='CCVCB' and (GETDATE()-hslht.NgayHieuLuc>900) and hsl.Ma_BL>=1  and MONTH(hslht.NgayHieuLuc)=MONTH(@NgayHieuLuc)", pr);

            //DataTable dt1 = new DataTable();
            //sqlHelper.Fill(dt1, "select cb.HoTen, dmdv.TenDV, hsl.Ma_HS_Luong, lng.TenLoaiNgach, cbnl.NgayHieuLuc from  CAN_BO cb, CANBO_NGACH_LUONG cbnl, HESO_LUONG hsl,  LOAI_NGACH lng, DANHMUC_DONVI dmdv where cb.Ma_CB=cbnl.Ma_CB and cbnl.Ma_HS_Luong=hsl.Ma_HS_Luong and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and cb.Ma_DV=dmdv.Ma_DV and (MONTH(cbnl.NgayHieuLuc) in (select MONTH(cbnl.NgayHieuLuc) from CAN_BO cb, CANBO_NGACH_LUONG cbnl, HESO_LUONG hsl,  LOAI_NGACH lng, DANHMUC_DONVI dmdv where cb.Ma_CB=cbnl.Ma_CB and cbnl.Ma_HS_Luong=hsl.Ma_HS_Luong and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and cb.Ma_DV=dmdv.Ma_DV and  (GETDATE()-cbnl.NgayHieuLuc>1260) and lng.Ma_Loai_Ngach!='CCVCB' and cbnl.Ma_BL=1)) and MONTH(cbnl.NgayHieuLuc)=MONTH(@NgayHieuLuc)", pr1);

            DataTable dt2 = new DataTable();
            sqlHelper.Fill(dt2, "select hslht.Ma_CB, hslht.HoTen, dmdv.TenDV, hslht.HSL,lng.TenLoaiNgach, hslht.NgayHieuLuc from HSL_HIENTAI hslht, HESO_LUONG hsl,  LOAI_NGACH lng, CAN_BO cb, DANHMUC_DONVI dmdv, CANBO_NGACH_LUONG cbnl where cb.Ma_DV=dmdv.Ma_DV and hsl.Ma_HS_Luong=cbnl.Ma_HS_Luong and cbnl.Ma_CB=cb.Ma_CB and  hslht.HSL=hsl.Ma_HS_Luong	and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach	and lng.Ma_Loai_Ngach='CCVCB' and (GETDATE()-hslht.NgayHieuLuc>540) and hsl.Ma_BL>=1 and MONTH(hslht.NgayHieuLuc)=MONTH(@NgayHieuLuc)", pr2);

            //DataTable dt3 = new DataTable();
            //sqlHelper.Fill(dt3, "select cb.HoTen, dmdv.TenDV, hsl.Ma_HS_Luong, lng.TenLoaiNgach, cbnl.NgayHieuLuc from  CAN_BO cb, CANBO_NGACH_LUONG cbnl, HESO_LUONG hsl,  LOAI_NGACH lng, DANHMUC_DONVI dmdv where cb.Ma_CB=cbnl.Ma_CB and cbnl.Ma_HS_Luong=hsl.Ma_HS_Luong and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and cb.Ma_DV=dmdv.Ma_DV and (MONTH(cbnl.NgayHieuLuc) in (select MONTH(cbnl.NgayHieuLuc) from CAN_BO cb, CANBO_NGACH_LUONG cbnl, HESO_LUONG hsl,  LOAI_NGACH lng, DANHMUC_DONVI dmdv where cb.Ma_CB=cbnl.Ma_CB and cbnl.Ma_HS_Luong=hsl.Ma_HS_Luong and hsl.Ma_Loai_Ngach=lng.Ma_Loai_Ngach and cb.Ma_DV=dmdv.Ma_DV and  (GETDATE()-cbnl.NgayHieuLuc>540) and lng.Ma_Loai_Ngach='CCVCB' and cbnl.Ma_BL=1)) and MONTH(cbnl.NgayHieuLuc)=MONTH(@NgayHieuLuc)", pr3);
            
            List<SAPLENLUONG> listSLLtheoThang = new List<SAPLENLUONG>();


            foreach (DataRow dr in dt.Rows)
            {
                SAPLENLUONG sll = new SAPLENLUONG();
                sll.Ma_CB = dr[0].ToString();
                sll.HoTen = dr[1].ToString();
                sll.NgayLLTRuoc = DateTime.Parse(dr[5].ToString());

                listSLLtheoThang.Add(sll);
            }
            //foreach (DataRow dr1 in dt1.Rows)
            //{
            //    SAPLENLUONG sll = new SAPLENLUONG();
            //    sll.HoTen = dr1[0].ToString();
            //    sll.TenDV = dr1[1].ToString();
            //    sll.Ma_HS_Luong = float.Parse(dr1[2].ToString());
            //    sll.TenLoaiNgach = dr1[3].ToString();
            //    sll.NgayLLTRuoc = DateTime.Parse(dr1[4].ToString());
            //    listSLLtheoThang.Add(sll);
            //}
            foreach (DataRow dr2 in dt2.Rows)
            {
                SAPLENLUONG sll = new SAPLENLUONG();
                sll.HoTen = dr2[0].ToString();
                sll.NgayLLTRuoc = DateTime.Parse(dr2[4].ToString());
                listSLLtheoThang.Add(sll);
            }
            //foreach (DataRow dr3 in dt3.Rows)
            //{
            //    SAPLENLUONG sll = new SAPLENLUONG();
            //    sll.HoTen = dr3[0].ToString();
            //    sll.TenDV = dr3[1].ToString();
            //    sll.Ma_HS_Luong = float.Parse(dr3[2].ToString());
            //    sll.TenLoaiNgach = dr3[3].ToString();
            //    sll.NgayLLTRuoc = DateTime.Parse(dr3[4].ToString());
            //    listSLLtheoThang.Add(sll);
            //}
            return listSLLtheoThang;
        }





        public List<CAN_BO> getLastMa_CB()
        {
            DataTable dtLastID = new DataTable();
            sqlHelper.Fill(dtLastID, "select TOP 1 Ma_CB from CAN_BO ORDER BY  Ma_CB DESC");

            List<CAN_BO> listLastID = new List<CAN_BO>();

            foreach (DataRow dr in dtLastID.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.Ma_CB = dr[0].ToString();

                listLastID.Add(cb);
            }
            return listLastID;
        }


        public List<THONGKEKT> getTKKTtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];

            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;
            

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and dmdv.Ma_DV=@Ma_DV", pr);

            List<THONGKEKT> listThongKeKT = new List<THONGKEKT>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKT tk = new THONGKEKT();
                tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[1].ToString();
                tk.TenDV = dr[2].ToString();
                tk.NoiDung = dr[3].ToString();
                tk.NgayKy = DateTime.Parse(dr[4].ToString());

                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }


        public List<THONGKEKT> getTKKTtheoNamDVMaKT(int Ma_KT, string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            pr[1] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[1].Value = Ma_DV;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = NgayKy;


            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and dmdv.Ma_DV=@Ma_DV and kt.Ma_KT=@Ma_KT", pr);

            List<THONGKEKT> listThongKeKT = new List<THONGKEKT>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKT tk = new THONGKEKT();
                tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[1].ToString();
                tk.TenDV = dr[2].ToString();
                tk.NoiDung = dr[3].ToString();
                tk.NgayKy = DateTime.Parse(dr[4].ToString());

                listThongKeKT.Add(tk);
            }
            return listThongKeKT;
        }


        public List<THONGKEKL> getTTKLtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];

            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;


            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and dmdv.Ma_DV=@Ma_DV", pr);

            List<THONGKEKL> listThongKeKL = new List<THONGKEKL>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKL tk = new THONGKEKL();
                //tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[0].ToString();
                tk.TenDV = dr[1].ToString();
                tk.NoiDung = dr[2].ToString();
                tk.NgayKy = DateTime.Parse(dr[3].ToString());

                listThongKeKL.Add(tk);
            }
            return listThongKeKL;
        }

        public List<THONGKEKL> getTKKLtheoNamDVMaKL(int Ma_KL, string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            pr[1] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[1].Value = Ma_DV;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = NgayKy;


            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and dmdv.Ma_DV=@Ma_DV and kl.Ma_KL=@Ma_KL", pr);

            List<THONGKEKL> listThongKeKL = new List<THONGKEKL>();
            foreach (DataRow dr in dt.Rows)
            {
                THONGKEKL tk = new THONGKEKL();
                //tk.Ma_CB = dr[0].ToString();
                tk.HoTen = dr[0].ToString();
                tk.TenDV = dr[1].ToString();
                tk.NoiDung = dr[2].ToString();
                tk.NgayKy = DateTime.Parse(dr[3].ToString());

                listThongKeKL.Add(tk);
            }
            return listThongKeKL;
        }


        public int countKTtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countKTtheoNamDVMaKT(int Ma_KT, string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_KT", SqlDbType.Int);
            pr[0].Value = Ma_KT;
            pr[1] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[1].Value = Ma_DV;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and kt.Ma_KT=@Ma_KT and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }


        public int countKLtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countKLtheoNamDVMaKT(int Ma_KL, string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_KL", SqlDbType.Int);
            pr[0].Value = Ma_KL;
            pr[1] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[1].Value = Ma_DV;
            pr[2] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[2].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cbkl.Ma_KL=kl.Ma_KL and cb.Ma_DV=dmdv.Ma_DV and kl.Ma_KL=@Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy))", pr);

            int c = dt.Rows.Count;

            return c;
        }


        public int countAllKTtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            throw new NotImplementedException();
        }

        public int countLDTTtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and kt.Ma_KT=1", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDCStheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and kt.Ma_KT=2", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDCBtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and kt.Ma_KT=3", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countTDTQtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, dmdv.TenDV, cbkt.NoiDung, cbkt.NgayKy from CANBO_KHENTHUONG cbkt, CAN_BO cb, KHEN_THUONG kt, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkt.Ma_CB and cbkt.Ma_KT=kt.Ma_KT and cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkt.NgayKy))=(YEAR(@NgayKy)) and kt.Ma_KT=4", pr);

            int c = dt.Rows.Count;

            return c;
        }


        public int countKhientrachtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=1", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countCanhcaotheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=2", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countHabacluongtheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=3", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countGiangchuctheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=4", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countCachchuctheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=5", pr);

            int c = dt.Rows.Count;

            return c;
        }

        public int countBuocthoiviectheoNamDV(string Ma_DV, DateTime NgayKy)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            pr[1] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            pr[1].Value = NgayKy;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.HoTen, dmdv.TenDV, cbkl.NoiDung, cbkl.NgayKy from CANBO_KYLUAT cbkl, CAN_BO cb, KY_LUAT kl, DANHMUC_DONVI dmdv where cb.Ma_CB=cbkl.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and cbkl.Ma_KL=kl.Ma_KL and dmdv.Ma_DV=@Ma_DV and (YEAR(cbkl.NgayKy))=(YEAR(@NgayKy)) and kl.Ma_KL=6", pr);

            int c = dt.Rows.Count;

            return c;
        }


        public List<HSL_HIENTAI> getHSLHienTai()
        {
            DataTable dt = new DataTable();
            List<HSL_HIENTAI> ListHSLHienTai = new List<HSL_HIENTAI>();
            sqlHelper.Fill(dt,"select *from HSL_HIENTAI");

            foreach (DataRow dr in dt.Rows)
            {
                HSL_HIENTAI hslht = new HSL_HIENTAI();
                hslht.Ma_CB = dr[0].ToString();
                hslht.HoTen = dr[1].ToString();
                hslht.Ten_Ngach = dr[2].ToString();
                hslht.Ma_BL = Int32.Parse(dr[3].ToString());
                hslht.QuyetDinh = dr[4].ToString();
                hslht.NgayHieuLuc = DateTime.Parse(dr[5].ToString());

                ListHSLHienTai.Add(hslht);
            }
            return ListHSLHienTai;
        }

        public int addHSLHienTai(HSL_HIENTAI hslht)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = hslht.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = hslht.HoTen;
            pr[2] = new SqlParameter("@Ten_Ngach", SqlDbType.Char);
            pr[2].Value=hslht.Ten_Ngach;
            pr[3] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[3].Value = hslht.Ma_BL;
            pr[4] = new SqlParameter("@QuyetDinh", SqlDbType.NVarChar);
            pr[4].Value = hslht.QuyetDinh;
            pr[5] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr[5].Value = hslht.NgayHieuLuc;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = hslht.Ma_CB;
            sqlHelper.Fill(check, "select Ma_CB from HSL_HIENTAI where Ma_CB=@Ma_CB", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert HSL_HIENTAI (Ma_CB, HoTen, Ten_Ngach, Ma_BL, QuyetDinh, NgayHieuLuc) values (@Ma_CB, @HoTen, @Ten_Ngach, @Ma_BL, @QuyetDinh, @NgayHieuLuc)",pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updateHSLHienTai(HSL_HIENTAI hslht)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = hslht.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = hslht.HoTen;
            pr[2] = new SqlParameter("@Ten_Ngach", SqlDbType.Char);
            pr[2].Value = hslht.Ten_Ngach;
            pr[3] = new SqlParameter("@Ma_BL", SqlDbType.Int);
            pr[3].Value = hslht.Ma_BL;
            pr[4] = new SqlParameter("@QuyetDinh", SqlDbType.NVarChar);
            pr[4].Value = hslht.QuyetDinh;
            pr[5] = new SqlParameter("@NgayHieuLuc", SqlDbType.DateTime);
            pr[5].Value = hslht.NgayHieuLuc;

            int kq;
            kq=sqlHelper.ExecuteNonQuery("update HSL_HIENTAI set HoTen=@HoTen, Ten_Ngach=@Ten_Ngach, Ma_BL=@Ma_BL, QuyetDinh=@QuyetDinh, NgayHieuLuc=@NgayHieuLuc where Ma_CB=@Ma_CB", pr);
            return kq;
        }


        public List<CAN_BO> getTenCBtheoMaCB(string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select HoTen from CAN_BO where Ma_CB=@Ma_CB",pr);

            List<CAN_BO> ListTenCB = new List<CAN_BO>();

            foreach (DataRow dr in dt.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.HoTen = dr[0].ToString();

                ListTenCB.Add(cb);
            }
            return ListTenCB;
        }


        public List<NGACH> getNgachTheoCB(string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select Ten_Ngach from HSL_HIENTAI where Ma_CB=@Ma_CB",pr);

            List<NGACH> listNgachTheoCB = new List<NGACH>();

            foreach (DataRow dr in dt.Rows)
            {
                NGACH ng = new NGACH();
                ng.Ma_Ngach = dr[0].ToString();
                
                listNgachTheoCB.Add(ng);
            }
            return listNgachTheoCB;
        }


        public List<BAC_LUONG> getBLtheoCB(string Ma_CB)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select Ma_BL as BL_HienTai from HSL_HIENTAI where Ma_CB=@Ma_CB", pr);

            List<BAC_LUONG> listBLtheoCB = new List<BAC_LUONG>();

            foreach (DataRow dr in dt.Rows)
            {
                BAC_LUONG bl = new BAC_LUONG();
                bl.Ma_BL = Int32.Parse(dr[0].ToString());

                listBLtheoCB.Add(bl);
            }
            return listBLtheoCB;

        }


        public List<PHI_TRU> getPT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from PHI_TRU where TrangThai=1");
            List<PHI_TRU> listPT = new List<PHI_TRU>();
            foreach (DataRow dr in dt.Rows)
            {
                PHI_TRU pt = new PHI_TRU();
                pt.Ma_PT = dr[0].ToString();
                pt.TenPT = dr[1].ToString();
                pt.HeSoPT = float.Parse(dr[2].ToString());

                listPT.Add(pt);
            }
            return listPT;
        }

        public int addPT(PHI_TRU pt)
        {
            SqlParameter[] pr=new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PT", SqlDbType.Char);
            pr[0].Value = pt.Ma_PT;
            pr[1] = new SqlParameter("@TenPT", SqlDbType.NVarChar);
            pr[1].Value = pt.TenPT;
            pr[2] = new SqlParameter("@HeSoPT", SqlDbType.Float);
            pr[2].Value = pt.HeSoPT;

            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_PT", SqlDbType.Char);
            pr1[0].Value = pt.Ma_PT;
            DataTable check = new DataTable();
            sqlHelper.Fill(check, "select Ma_PT from PHI_TRU where Ma_PT=@Ma_PT", pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert PHI_TRU (Ma_PT, TenPT, HeSoPT, TrangThai) values (@Ma_PT, @TenPT, @HeSoPT, 1)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }


        public int updatePT(PHI_TRU pt)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PT", SqlDbType.Char);
            pr[0].Value = pt.Ma_PT;
            pr[1] = new SqlParameter("@TenPT", SqlDbType.NVarChar);
            pr[1].Value = pt.TenPT;
            pr[2] = new SqlParameter("@HeSoPT", SqlDbType.Float);
            pr[2].Value = pt.HeSoPT;
          
            int  kq = sqlHelper.ExecuteNonQuery("update PHI_TRU set TenPT=@TenPT, HeSoPT=@HeSoPT where Ma_PT=@Ma_PT", pr);
          
            return kq;
        }

        public void deletePT(string Ma_PT)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_PT", SqlDbType.Char);
            pr[0].Value = Ma_PT;
            sqlHelper.ExecuteNonQuery("update PHI_TRU set TrangThai=0 where Ma_PT=@Ma_PT",pr);
        }


        public List<PHUCAP_THAMNIEN> getPCTN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from PHUCAP_THAMNIEN");
            List<PHUCAP_THAMNIEN> listPCTN = new List<PHUCAP_THAMNIEN>();
            foreach (DataRow dr in dt.Rows)
            {
                PHUCAP_THAMNIEN pctn = new PHUCAP_THAMNIEN();
                pctn.Ma_PCTN = Int32.Parse(dr[0].ToString());
                pctn.TenPCTN = dr[1].ToString();
                pctn.HeSoPCTN = Int32.Parse(dr[2].ToString());

                listPCTN.Add(pctn);
            }
            return listPCTN;
        }

        public int addPCTN(PHUCAP_THAMNIEN pctn)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PCTN", SqlDbType.Int);
            pr[0].Value = pctn.Ma_PCTN;
            pr[1] = new SqlParameter("@TenPCTN", SqlDbType.NVarChar);
            pr[1].Value = pctn.TenPCTN;
            pr[2] = new SqlParameter("@HeSoPCTN", SqlDbType.Int);
            pr[2].Value = pctn.HeSoPCTN;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_PCTN", SqlDbType.Int);
            pr1[0].Value = pctn.Ma_PCTN;

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert PHUCAP_THAMNIEN (Ma_PCTN, TenPCTN, HeSoPCTN) values (@Ma_PCTN, @TenPCTN, @HeSoPCTN)", pr);
            }
            else
            {
                kq = 1;
            }
            return kq;
        }

        public int updatePCTN(PHUCAP_THAMNIEN pctn)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PCTN", SqlDbType.Int);
            pr[0].Value = pctn.Ma_PCTN;
            pr[1] = new SqlParameter("@TenPCTN", SqlDbType.NVarChar);
            pr[1].Value = pctn.TenPCTN;
            pr[2] = new SqlParameter("@HeSoPCTN", SqlDbType.Int);
            pr[2].Value = pctn.HeSoPCTN;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update PHUCAP_THAMNIEN set TenPCTN=@TenPCTN, HeSoPCTN=@HeSoPCTN where Ma_PCTN=@Ma_PCTN", pr);
         
           return kq;
        }

        public void deletePCTN(string Ma_PCTN)
        {
            throw new NotImplementedException();
        }


        public void deletePCTN(int Ma_PCTN)
        {
            throw new NotImplementedException();
        }


        public List<PHUCAP_THAMNIEN> getLastPCTN()
        {
            DataTable lastID = new DataTable();
            sqlHelper.Fill(lastID, "select TOP 1 Ma_PCTN from PHUCAP_THAMNIEN ORDER BY Ma_PCTN DESC");
            List<PHUCAP_THAMNIEN> listLastID = new List<PHUCAP_THAMNIEN>();
            foreach (DataRow dr in lastID.Rows)
            {
                PHUCAP_THAMNIEN pctn = new PHUCAP_THAMNIEN();
                pctn.Ma_PCTN = Int32.Parse(dr[0].ToString());
                listLastID.Add(pctn);
            }
            return listLastID;
        }


        public List<PHUCAP_KIEMNHIEM> getPCKN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select * from PHUCAP_KIEMNHIEM");
            List<PHUCAP_KIEMNHIEM> listPCKN = new List<PHUCAP_KIEMNHIEM>();

            foreach (DataRow dr in dt.Rows)
            {
                PHUCAP_KIEMNHIEM pckn = new PHUCAP_KIEMNHIEM();
                pckn.Ma_PCKN = Int32.Parse(dr[0].ToString());
                pckn.TenPCKN = dr[1].ToString();
                pckn.HeSoPCKN = float.Parse(dr[2].ToString());
                pckn.Ma_LoaiPCKN = Int32.Parse(dr[3].ToString());
                pckn.Ma_Loai_DV = dr[4].ToString();
                listPCKN.Add(pckn);
            }
            return listPCKN;
        }

        public int addPCKN(PHUCAP_KIEMNHIEM pckn)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr[0].Value = pckn.Ma_PCKN;
            pr[1] = new SqlParameter("@TenPCKN", SqlDbType.NVarChar);
            pr[1].Value = pckn.TenPCKN;
            pr[2] = new SqlParameter("@HeSoPCKN", SqlDbType.Float);
            pr[2].Value = pckn.HeSoPCKN;
            pr[3] = new SqlParameter("@Ma_LoaiPCKN", SqlDbType.Int);
            pr[3].Value = pckn.Ma_LoaiPCKN;
            pr[4] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[4].Value = pckn.Ma_Loai_DV;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr1[0].Value = pckn.Ma_PCKN;
            sqlHelper.Fill(check,"select Ma_PCKN from PHUCAP_KIEMNHIEM where Ma_PCKN=@Ma_PCKN",pr1);
            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert PHUCAP_KIEMNHIEM (Ma_PCKN, TenPCKN, HeSoPCKN, Ma_LoaiPCKN, Ma_Loai_DV) values (@Ma_PCKN, @TenPCKN, @HeSoPCKN, @Ma_LoaiPCKN, @Ma_Loai_DV)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;
        }

        public int updatepcKN(PHUCAP_KIEMNHIEM pckn)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr[0].Value = pckn.Ma_PCKN;
            pr[1] = new SqlParameter("@TenPCKN", SqlDbType.NVarChar);
            pr[1].Value = pckn.TenPCKN;
            pr[2] = new SqlParameter("@HeSoPCKN", SqlDbType.Float);
            pr[2].Value = pckn.HeSoPCKN;
            pr[3] = new SqlParameter("@Ma_LoaiPCKN", SqlDbType.Int);
            pr[3].Value = pckn.Ma_LoaiPCKN;
            pr[4] = new SqlParameter("@Ma_Loai_DV", SqlDbType.Char);
            pr[4].Value = pckn.Ma_Loai_DV;

            int kq = sqlHelper.ExecuteNonQuery("update PHUCAP_KIEMNHIEM set TenPCKN=@TenPCKN, HeSoPCKN=@HeSoPCKN, Ma_LoaiPCKN=@Ma_LoaiPCKN, Ma_Loai_DV=@Ma_Loai_DV where Ma_PCKN=@Ma_PCKN", pr);
           
            return kq;
        }

        public void deletePCKN(int Ma_PCKN)
        {
            throw new NotImplementedException();
        }

        public List<LOAI_PCKN> getLPCKN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from LOAI_PCKN");
            List<LOAI_PCKN> listLPCKN = new List<LOAI_PCKN>();

            foreach (DataRow dr in dt.Rows)
            {
                LOAI_PCKN lpckn = new LOAI_PCKN();
                lpckn.Ma_LoaiPCKN = Int32.Parse(dr[0].ToString());
                lpckn.TenLPCKN = dr[1].ToString();

                listLPCKN.Add(lpckn);
            }
            return listLPCKN;
        }

        public int addLPCKN(LOAI_PCKN lpckn)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_LoaiPCKN", SqlDbType.Int);
            pr[0].Value = lpckn.Ma_LoaiPCKN;
            pr[1] = new SqlParameter("@TenLPCKN", SqlDbType.NVarChar);
            pr[1].Value = lpckn.TenLPCKN;

            int kq = sqlHelper.ExecuteNonQuery("insert LOAI_PCKN (Ma_LoaiPCKN, TenLPCKN) values (@Ma_LoaiPCKN, @TenLPCKN)",pr);
            return kq;

        }

        public int updateLPCKN(LOAI_PCKN lpckn)
        {
            throw new NotImplementedException();
        }

        public void deleteLPCKN(int Ma_LoaiPCKN)
        {
            throw new NotImplementedException();
        }


        public List<PHUCAP_KIEMNHIEM> getLastPCKN()
        {
            DataTable lastID = new DataTable();
            sqlHelper.Fill(lastID,"select TOP 1 Ma_PCKN from PHUCAP_KIEMNHIEM ORDER BY Ma_PCKN DESC");
            List<PHUCAP_KIEMNHIEM> listLastID = new List<PHUCAP_KIEMNHIEM>();
            foreach (DataRow dr in lastID.Rows)
            {
                PHUCAP_KIEMNHIEM pckn = new PHUCAP_KIEMNHIEM();
                pckn.Ma_PCKN = Int32.Parse(dr[0].ToString());

                listLastID.Add(pckn);
            }
            return listLastID;
        }

        public List<LOAI_PCKN> getLastLPCKN()
        {
            DataTable lastID = new DataTable();
            sqlHelper.Fill(lastID,"select TOP 1 Ma_LoaiPCKN from LOAI_PCKN ORDER BY Ma_LoaiPCKN DESC");
            List<LOAI_PCKN> listLastLoaiPCKN = new List<LOAI_PCKN>();

            foreach (DataRow dr in lastID.Rows)
            {
                LOAI_PCKN lpckn = new LOAI_PCKN();
                lpckn.Ma_LoaiPCKN = Int32.Parse(dr[0].ToString());

                listLastLoaiPCKN.Add(lpckn);
            }
            return listLastLoaiPCKN;
        }








        public List<PHUCAP_GIAOVIEN> getPCGV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from PHUCAP_GIAOVIEN");
            List<PHUCAP_GIAOVIEN> listPCGV = new List<PHUCAP_GIAOVIEN>();

            foreach (DataRow dr in dt.Rows)
            {
                PHUCAP_GIAOVIEN pcgv = new PHUCAP_GIAOVIEN();
                pcgv.Ma_PCGV = Int32.Parse(dr[0].ToString());
                pcgv.TenPCGV = dr[1].ToString();
                pcgv.HeSoPCGV = float.Parse(dr[2].ToString());

                listPCGV.Add(pcgv);
            }
            return listPCGV;
        }

        public int addPCGV(PHUCAP_GIAOVIEN pcgv)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PCGV", SqlDbType.Int);
            pr[0].Value = pcgv.Ma_PCGV;
            pr[1] = new SqlParameter("@TenPCGV", SqlDbType.NVarChar);
            pr[1].Value = pcgv.TenPCGV;
            pr[2] = new SqlParameter("@HeSoPCGV", SqlDbType.Float);
            pr[2].Value = pcgv.HeSoPCGV;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert PHUCAP_GIAOVIEN (Ma_PCGV, TenPCGV, HeSoPCGV) values (@Ma_PCGV, @TenPCGV, @HeSoPCGV)",pr);
            return kq;

        }

        public int updatePCGV(PHUCAP_GIAOVIEN pcgv)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_PCGV", SqlDbType.Int);
            pr[0].Value = pcgv.Ma_PCGV;
            pr[1] = new SqlParameter("@TenPCGV", SqlDbType.NVarChar);
            pr[1].Value = pcgv.TenPCGV;
            pr[2] = new SqlParameter("@HeSoPCGV", SqlDbType.Float);
            pr[2].Value = pcgv.HeSoPCGV;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update PHUCAP_GIAOVIEN set TenPCGV=@TenPCGV, HeSoPCGV=@HeSoPCGV where Ma_PCGV=@Ma_PCGV", pr);
            return kq;
        }

        public void deletePCGV(int Ma_PCGV)
        {
            throw new NotImplementedException();
        }


        public List<PHUCAP_GIAOVIEN> getLastPCGV()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select TOP 1 Ma_PCGV from PHUCAP_GIAOVIEN ORDER BY Ma_PCGV DESC");
            List<PHUCAP_GIAOVIEN> listPCGV = new List<PHUCAP_GIAOVIEN>();
            foreach (DataRow dr in dt.Rows)
            {
                PHUCAP_GIAOVIEN pcgv = new PHUCAP_GIAOVIEN();
                pcgv.Ma_PCGV = Int32.Parse(dr[0].ToString());

                listPCGV.Add(pcgv);
            }
            return listPCGV;
        }


        public List<CANBO_PCKN> getCBPCKN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select * from CANBO_PCKN");
            List<CANBO_PCKN> listCBPCKN = new List<CANBO_PCKN>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_PCKN cbpckn = new CANBO_PCKN();
                cbpckn.Ma_CB = dr[0].ToString();
                cbpckn.Ma_PCKN = Int32.Parse(dr[1].ToString());
                cbpckn.Ma_DV = dr[2].ToString();
                cbpckn.QuyetDinhKN = dr[3].ToString();
                cbpckn.NgayKyKN = DateTime.Parse(dr[4].ToString());
                cbpckn.FileQuyetDinhKN = (byte[])dr[5];
                //cbpckn.TrangThaiKN = bool.Parse(dr[6].ToString());
                listCBPCKN.Add(cbpckn);
            }
            return listCBPCKN;
        }

        public int addCBPCKN(CANBO_PCKN cbpckn)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbpckn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr[1].Value = cbpckn.Ma_PCKN;
            pr[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[2].Value = cbpckn.Ma_DV;
            pr[3] = new SqlParameter("@QuyetDinhKN", SqlDbType.Char);
            pr[3].Value = cbpckn.QuyetDinhKN;
            pr[4] = new SqlParameter("@NgayKyKN", SqlDbType.DateTime);
            pr[4].Value = cbpckn.NgayKyKN;
            pr[5] = new SqlParameter("@FileQuyetDinhKN", SqlDbType.Binary);
            pr[5].Value = cbpckn.FileQuyetDinhKN;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[3];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = cbpckn.Ma_CB;
            pr1[1] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr1[1].Value = cbpckn.Ma_PCKN;
            pr1[2] = new SqlParameter("@Ma_DV",SqlDbType.Char);
            pr1[2].Value = cbpckn.Ma_DV;
            
            sqlHelper.Fill(check, "select Ma_CB, Ma_PCKN, Ma_DV from CANBO_PCKN where Ma_PCKN=@Ma_PCKN and Ma_DV=@Ma_DV",pr1);

            int kq;
            if (check.Rows.Count == 0)
            {
                kq = sqlHelper.ExecuteNonQuery("insert CANBO_PCKN (Ma_CB, Ma_PCKN, Ma_DV, QuyetDinhKN, NgayKyKN, FileQuyetDinhKN, TrangThaiKN) values (@Ma_CB, @Ma_PCKN, @Ma_DV, @QuyetDinhKN, @NgayKyKN, @FileQuyetDinhKN, 1)", pr);
            }
            else
            {
                kq = -1;
            }
            return kq;

        }

        public int updateCBPCKN(CANBO_PCKN cbpckn)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbpckn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr[1].Value = cbpckn.Ma_PCKN;
            pr[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[2].Value = cbpckn.Ma_DV;
            pr[3] = new SqlParameter("@QuyetDinhKN", SqlDbType.Char);
            pr[3].Value = cbpckn.QuyetDinhKN;
            pr[4] = new SqlParameter("@NgayKyKN", SqlDbType.DateTime);
            pr[4].Value = cbpckn.NgayKyKN;
            pr[5] = new SqlParameter("@FileQuyetDinhKN", SqlDbType.Binary);
            pr[5].Value = cbpckn.FileQuyetDinhKN;

            
            int kq;
           
                kq = sqlHelper.ExecuteNonQuery("update CANBO_PCKN set QuyetDinhKN=@QuyetDinhKN, NgayKyKN=@NgayKyKN, FileQuyetDinhKN=@FileQuyetDinhKN where Ma_CB=@Ma_CB and Ma_PCKN=@Ma_PCKN and Ma_DV=@Ma_DV", pr);
          
            return kq;
        }

        public void deleteCBPCKN(string Ma_CB, int Ma_PCKN, string Ma_DV)
        {
            SqlParameter[] pr1 = new SqlParameter[3];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = Ma_CB;
            pr1[1] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr1[1].Value = Ma_PCKN;
            pr1[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[2].Value = Ma_DV;

            sqlHelper.ExecuteNonQuery("update CANBO_PCKN set TrangThaiKN=0 where Ma_CB=@Ma_CB and Ma_PCKN=@Ma_PCKN and Ma_DV=@Ma_DV", pr1);
        }


        //======================get don vi theo loai phu cap kiem nhiem==================
        public List<DANHMUC_DONVI> getDVtheoPCKN(int Ma_PCKN)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr[0].Value = Ma_PCKN;

            List<DANHMUC_DONVI> listDVtheoPCKN = new List<DANHMUC_DONVI>();

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select dmdv.Ma_DV, dmdv.TenDV from PHUCAP_KIEMNHIEM pckn, LOAI_DV ldv, DANHMUC_DONVI dmdv where pckn.Ma_Loai_DV=ldv.Ma_Loai_DV and ldv.Ma_Loai_DV=dmdv.Ma_Loai_DV	and pckn.Ma_PCKN=@Ma_PCKN", pr);

            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dmdv = new DANHMUC_DONVI();
                dmdv.Ma_DV = dr[0].ToString();
                dmdv.TenDVTT = dr[1].ToString();

                listDVtheoPCKN.Add(dmdv);
            }
            return listDVtheoPCKN;
        }


        
       

        public int checkCBKN(int Ma_PCKN, string Ma_DV)
        {
            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[2];
            pr1[1] = new SqlParameter("@Ma_PCKN", SqlDbType.Int);
            pr1[1].Value = Ma_PCKN;
            pr1[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[2].Value = Ma_DV;

            sqlHelper.Fill(check, "select Ma_CB, Ma_PCKN, Ma_DV from CANBO_PCKN where Ma_PCKN=@Ma_PCKN and Ma_DV=@Ma_DV", pr1);

            int kq;
            if (check.Rows.Count > 0)
                kq = 1;
            else
                kq = -1;
            return kq;
        }

        public int checkCBCV(string Ma_CV, string Ma_DV)
        {
            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[2];
            
            pr1[1] = new SqlParameter("@Ma_CV", SqlDbType.Char);
            pr1[1].Value = Ma_CV;
            pr1[2] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[2].Value = Ma_DV;
            sqlHelper.Fill(check, "select Ma_CV, Ma_DV from CANBO_CHUCVU where Ma_CV=@Ma_CV and Ma_DV=@Ma_DV", pr1);
            int kq;
            if (check.Rows.Count > 0)
                kq = 1;
            else
                kq = -1;
            return kq;
        }





        public List<CANBO_PCTHAMNIEN> getCBPCTN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CANBO_PCTHAMNIEN");
            List<CANBO_PCTHAMNIEN> listCBPCTN = new List<CANBO_PCTHAMNIEN>();

            foreach (DataRow dr in dt.Rows)
            {
                CANBO_PCTHAMNIEN cbpctn = new CANBO_PCTHAMNIEN();
                cbpctn.Ma_CB = dr[0].ToString();
                cbpctn.Ma_PCTN = Int32.Parse(dr[1].ToString());
                cbpctn.QuyetDinhTN = dr[2].ToString();
                cbpctn.NgayKyTN = DateTime.Parse(dr[3].ToString());
                cbpctn.FileQuyetDinhTN = (byte[])dr[4];

                listCBPCTN.Add(cbpctn);
            }
            return listCBPCTN;
        }

        public int addCBPCTN(CANBO_PCTHAMNIEN cbpctn)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbpctn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_PCTN", SqlDbType.Int);
            pr[1].Value = cbpctn.Ma_PCTN;
            pr[2] = new SqlParameter("@QuyetDinhTN", SqlDbType.Char);
            pr[2].Value = cbpctn.QuyetDinhTN;
            pr[3] = new SqlParameter("@NgayKyTN", SqlDbType.DateTime);
            pr[3].Value = cbpctn.NgayKyTN;
            pr[4] = new SqlParameter("@FileQuyetDinhTN", SqlDbType.Binary);
            pr[4].Value = cbpctn.FileQuyetDinhTN;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_PCTHAMNIEN (Ma_CB, Ma_PCTN, QuyetDinhTN, NgayKyTN, FileQuyetDinhTN) values (@Ma_CB, @Ma_PCTN, @QuyetDinhTN, @NgayKyTN, @FileQuyetDinhTN)", pr);
            return kq;
        }

        public int updateCBPCTN(CANBO_PCTHAMNIEN cbpctn)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbpctn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_PCTN", SqlDbType.Int);
            pr[1].Value = cbpctn.Ma_PCTN;
            pr[2] = new SqlParameter("@QuyetDinhTN", SqlDbType.Char);
            pr[2].Value = cbpctn.QuyetDinhTN;
            pr[3] = new SqlParameter("@NgayKyTN", SqlDbType.DateTime);
            pr[3].Value = cbpctn.NgayKyTN;
            pr[4] = new SqlParameter("@FileQuyetDinhTN", SqlDbType.Binary);
            pr[4].Value = cbpctn.FileQuyetDinhTN;

            int kq;
            kq = sqlHelper.ExecuteNonQuery("update CANBO_PCTHAMNIEN set QuyetDinhTN=@QuyetDinhTN, NgayKyTN=@NgayKyTN, FileQuyetDinhTN=@FileQuyetDinhTN where Ma_CB=@Ma_CB and Ma_PCTN=@Ma_PCTN",pr);
            return kq;
        }

        public void deleteCBPCTN(string Ma_CB, int Ma_PCTN)
        {
            throw new NotImplementedException();
        }


        public List<DANHMUC_DONVI> getLastDVTT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select TOP 1 Ma_DV from DANHMUC_DONVI ORDER BY Ma_DV DESC");
            List<DANHMUC_DONVI> listLastDVTT = new List<DANHMUC_DONVI>();

            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dmdv = new DANHMUC_DONVI();
                dmdv.Ma_DV = dr[0].ToString();

                listLastDVTT.Add(dmdv);
            }
            return listLastDVTT;

        }





        public List<HSTN_HIENTAI> getHSTNHientai(string Ma_CB)
        {
            DataTable dt = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            sqlHelper.Fill(dt,"select GiaTriTN from HSTN_HIENTAI where Ma_CB=@Ma_CB", pr);
            List<HSTN_HIENTAI> listHSTNHienTai = new List<HSTN_HIENTAI>();

            foreach (DataRow dr in dt.Rows)
            {
                HSTN_HIENTAI tnht = new HSTN_HIENTAI();
                tnht.GiaTriTN = Int32.Parse(dr[0].ToString());

                listHSTNHienTai.Add(tnht);
            }
            return listHSTNHienTai;
        }


        public List<SAPTANGTN> getSapTangTN()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select Ma_CB, HoTen, NgayTuyenDung from CAN_BO	where MONTH(GETDATE())=MONTH(NgayTuyenDung)	and (YEAR(GETDATE())-YEAR(NgayTuyenDung))=5 and Ma_CB NOT IN (select Ma_CB from HSTN_HIENTAI)");
            DataTable dt1 = new DataTable();
            sqlHelper.Fill(dt1, "select Ma_CB, HoTen, GiaTriTN, NgayTN from HSTN_HIENTAI where MONTH(GETDATE())=MONTH(NgayTN) and (YEAR(GETDATE())-YEAR(NgayTN))=5");

            List<SAPTANGTN> listSapTangTN = new List<SAPTANGTN>();
            foreach (DataRow dr in dt.Rows)
            {
                SAPTANGTN sttn = new SAPTANGTN();
                sttn.Ma_CB = dr[0].ToString();
                sttn.HoTen = dr[1].ToString();
                sttn.GiaTriTN = 0;
                sttn.NgayTN = DateTime.Parse(dr[2].ToString());

                listSapTangTN.Add(sttn);
            }
            foreach (DataRow dr1 in dt1.Rows)
            {
                SAPTANGTN sttn = new SAPTANGTN();
                sttn.Ma_CB = dr1[0].ToString();
                sttn.HoTen = dr1[1].ToString();
                sttn.GiaTriTN = Int32.Parse(dr1[2].ToString());
                sttn.NgayTN = DateTime.Parse(dr1[3].ToString());

                listSapTangTN.Add(sttn);
            }
            return listSapTangTN;
        }


        public int addTNHienTai(HSTN_HIENTAI tnht)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = tnht.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = tnht.HoTen;
            pr[2] = new SqlParameter("@GiaTriTN", SqlDbType.Int);
            pr[2].Value = tnht.GiaTriTN;
            pr[3] = new SqlParameter("@QuyetDinhTN", SqlDbType.Char);
            pr[3].Value = tnht.QuyetDinhTN;
            pr[4] = new SqlParameter("@NgayTN", SqlDbType.DateTime);
            pr[4].Value = tnht.NgayTN;

            DataTable check = new DataTable();
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr1[0].Value = tnht.Ma_CB;
            sqlHelper.Fill(check,"select Ma_CB from HSTN_HIENTAI where Ma_CB=@Ma_CB",pr1);

            int kq;
            if (check.Rows.Count == 0)
                kq = sqlHelper.ExecuteNonQuery("insert HSTN_HIENTAI (Ma_CB, HoTen, GiaTriTN, QuyetDinhTN, NgayTN) values (@Ma_CB, @HoTen, @GiaTriTN, @QuyetDinhTN, @NgayTN)", pr);
            else
                kq = -1;
            return kq;
        }           
 

        public int updateTNHienTai(HSTN_HIENTAI tnht)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = tnht.Ma_CB;
            pr[1] = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            pr[1].Value = tnht.HoTen;
            pr[2] = new SqlParameter("@GiaTriTN", SqlDbType.Int);
            pr[2].Value = tnht.GiaTriTN;
            pr[3] = new SqlParameter("@QuyetDinhTN", SqlDbType.Char);
            pr[3].Value = tnht.QuyetDinhTN;
            pr[4] = new SqlParameter("@NgayTN", SqlDbType.DateTime);
            pr[4].Value = tnht.NgayTN;

            
            int kq;
            
                kq = sqlHelper.ExecuteNonQuery("update HSTN_HIENTAI set HoTen=@HoTen, GiaTriTN=@GiaTriTN, QuyetDinhTN=@QuyetDinhTN, NgayTN=@NgayTN where Ma_CB=@Ma_CB", pr);
            
            return kq;
            
         
        }


        public List<DANHMUC_DONVI> getDVTT_DV(string Ma_DV_HC)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = Ma_DV_HC;

            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select Ma_DV, TenDV, Ma_DV_CQ from DANHMUC_DONVI dmdv, DONVI_HANHCHINH dvhc where dvhc.Ma_DV_HC=dmdv.Ma_DV_CQ and dvhc.Ma_DV_HC=@Ma_DV_HC",pr);

            List<DANHMUC_DONVI> listDVTT_DV = new List<DANHMUC_DONVI>();
            foreach (DataRow dr in dt.Rows)
            {
                DANHMUC_DONVI dmdv = new DANHMUC_DONVI();
                dmdv.Ma_DV = dr[0].ToString();
                dmdv.TenDVTT = dr[1].ToString();
                dmdv.Ma_DV_CQ = dr[2].ToString();

                listDVTT_DV.Add(dmdv);
            }
            return listDVTT_DV;
        }


        public DataSet getCB_DV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;

            DataSet dts = new DataSet();
            sqlHelper.Fill(dts, "select cb.Ma_CB, cb.HoTen, cb.NamSinh, cb.GioiTinh, cb.QueQuan, cb.Ma_DV from CAN_BO cb, DANHMUC_DONVI dmdv where cb.Ma_DV=dmdv.Ma_DV and dmdv.Ma_DV=@Ma_DV", pr);
            return dts;
            
        }


        public List<SAPTANGTN> getCBTN_DV(string Ma_DV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr[0].Value = Ma_DV;
            SqlParameter[] pr1 = new SqlParameter[1];
            pr1[0] = new SqlParameter("@Ma_DV", SqlDbType.Char);
            pr1[0].Value = Ma_DV;
            
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select cb.Ma_CB, cb.HoTen, cb.NgayTuyenDung from CAN_BO cb, DANHMUC_DONVI dmdv where cb.Ma_DV=dmdv.Ma_DV and MONTH(GETDATE())=MONTH(cb.NgayTuyenDung)	and (YEAR(GETDATE())-YEAR(cb.NgayTuyenDung))=5 and cb.Ma_CB not in (select Ma_CB from HSTN_HIENTAI)	and dmdv.Ma_DV=@Ma_DV",pr);
            DataTable dt1 = new DataTable();
            sqlHelper.Fill(dt1, "select tnht.Ma_CB, tnht.HoTen, tnht.GiaTriTN, tnht.NgayTN from HSTN_HIENTAI tnht, CAN_BO cb, DANHMUC_DONVI dmdv where tnht.Ma_CB=cb.Ma_CB and cb.Ma_DV=dmdv.Ma_DV and  MONTH(GETDATE())=MONTH(tnht.NgayTN) and (YEAR(GETDATE())-YEAR(tnht.NgayTN))=5 and dmdv.Ma_DV=@Ma_DV", pr1);

            List<SAPTANGTN> listSapTangTN = new List<SAPTANGTN>();
            foreach (DataRow dr in dt.Rows)
            {
                SAPTANGTN sttn = new SAPTANGTN();
                sttn.Ma_CB = dr[0].ToString();
                sttn.HoTen = dr[1].ToString();
                sttn.GiaTriTN = 0;
                sttn.NgayTN = DateTime.Parse(dr[2].ToString());

                listSapTangTN.Add(sttn);
            }
            foreach (DataRow dr1 in dt1.Rows)
            {
                SAPTANGTN sttn = new SAPTANGTN();
                sttn.Ma_CB = dr1[0].ToString();
                sttn.HoTen = dr1[1].ToString();
                sttn.GiaTriTN = Int32.Parse(dr1[2].ToString());
                sttn.NgayTN = DateTime.Parse(dr1[3].ToString());

                listSapTangTN.Add(sttn);
            }
            return listSapTangTN;   
        }


        public List<HSTN_HIENTAI> getTNHT()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select *from HSTN_HIENTAI");
            List<HSTN_HIENTAI> listHSTNHienTai = new List<HSTN_HIENTAI>();

            foreach (DataRow dr in dt.Rows)
            {
                HSTN_HIENTAI tnht = new HSTN_HIENTAI();
                tnht.Ma_CB = dr[0].ToString();
                tnht.HoTen = dr[1].ToString();
                tnht.GiaTriTN = Int32.Parse(dr[2].ToString());
                tnht.QuyetDinhTN = dr[3].ToString();
                tnht.NgayTN = DateTime.Parse(dr[4].ToString());

                listHSTNHienTai.Add(tnht);
            }
            return listHSTNHienTai;
        }




        // check xem can bo da len luong chua...neu da len luong thi khong cho popup va thong bao da len luong dau tien roi
        public int checkQDCBNL(string Ma_CB)
        {
            DataTable check = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = Ma_CB;
            sqlHelper.Fill(check,"select Ma_CB from HSL_HIENTAI where Ma_CB=@Ma_CB",pr);

            int kq;
            if (check.Rows.Count == 0)
                kq = 1;
            else
                kq = -1;
            return kq;
        }


        public List<TAI_KHOAN> getTK()
        {
            throw new NotImplementedException();
        }

        public int checkTK(string Ma_TK, string MatKhau)
        {
            SqlParameter[] pr=new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TK", SqlDbType.Char);
            pr[0].Value = Ma_TK;
            pr[1] = new SqlParameter("@MatKhau", SqlDbType.Char);
            pr[1].Value = MatKhau;

            DataTable checkTK = new DataTable();
            sqlHelper.Fill(checkTK, "select Ma_TK, MatKhau from TAI_KHOAN where Ma_TK=@Ma_TK and MatKhau=@MatKhau",pr);

            int kq = checkTK.Rows.Count;
            if (kq == 0)
                kq = 1;
            else
                kq = -1;
            return kq;
        }

        //========================LCB======================

        public List<LCB> getLCB()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select *from LCB");
            List<LCB> listLCB = new List<LCB>();
            foreach (DataRow dr in dt.Rows)
            {
                LCB lcb = new LCB();
                lcb.LUONGCOBAN = float.Parse(dr[0].ToString());
                listLCB.Add(lcb);
            }
            return listLCB;
        }

        public int updateLCB(LCB lcb)
        {
            SqlParameter[] pr=new SqlParameter[1];
            pr[0] = new SqlParameter("@LCB", SqlDbType.Float);
            pr[0].Value = lcb.LUONGCOBAN;

            int kq = sqlHelper.ExecuteNonQuery("update LCB set LUONGCOBAN=@LCB", pr);
            return kq;
        }





        public int TinhLuong()
        {

            DataTable check = new DataTable();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@CheckNgay", SqlDbType.DateTime);
            pr[0].Value = DateTime.Today;

            sqlHelper.Fill(check, "select MONTH(ThangNam), YEAR(ThangNam) from TINHLUONG where MONTH(ThangNam)=MONTH(@CheckNgay) and YEAR(ThangNam)=YEAR(@CheckNgay)", pr);
            

            string str = "insert into TINHLUONG select cb.Ma_CB, cb.HoTen, (case when cbcv.Ma_CV is NULL then 'Nhân viên' else cv.TenCV end) as ChucVu, dmdv.TenDV,hsl.Ma_HS_Luong, ((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB))+(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)*( (select HeSoPCGV from PHUCAP_GIAOVIEN where Ma_PCGV=1)))/100))) +((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100) as Luong,(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHYT')/100) +(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHXH')/100) +(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHTN')/100)+(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='PCD')/100) as PhiTru,(((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB))+(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)*(select HeSoPCGV from PHUCAP_GIAOVIEN where Ma_PCGV=1))/100))) +((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)-((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHYT')/100) +(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHXH')/100) +(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='BHTN')/100)+(((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)+((((hsl.Ma_HS_Luong+(case when cv.HeSoCV is NULL then 0 else cv.HeSoCV end))*(select LUONGCOBAN from LCB)))*(case when hstnht.GiaTriTN is NULL then 0 else hstnht.GiaTriTN end))/100)*(select HeSoPT from PHI_TRU where Ma_PT='PCD')/100)) as ThucLanh,GETDATE() as ThangNam from CAN_BO cb LEFT JOIN CANBO_CHUCVU cbcv on cb.Ma_CB=cbcv.Ma_CB LEFT OUTER JOIN DANHMUC_DONVI dmdv on cb.Ma_DV=dmdv.Ma_DV LEFT JOIN CHUC_VU cv on cbcv.Ma_CV=cv.Ma_CV LEFT JOIN HSL_HIENTAI hslht on cb.Ma_CB=hslht.Ma_CB LEFT JOIN HSTN_HIENTAI hstnht on cb.Ma_CB=hstnht.Ma_CB LEFT JOIN NGACH ng on hslht.Ten_Ngach=ng.Ma_Ngach LEFT JOIN LOAI_NGACH lng on ng.Ma_Loai_Ngach=lng.Ma_Loai_Ngach LEFT JOIN HESO_LUONG hsl on lng.Ma_Loai_Ngach=hsl.Ma_Loai_Ngach and hslht.Ma_BL=hsl.Ma_BL";

            int kq;
            if (check.Rows.Count == 0)
                kq = sqlHelper.ExecuteNonQuery(str);
            else
                kq = -1;
             return kq;

        }

        public List<TINHLUONG> getLCBtheoThangNam(DateTime dt)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@LocNgay", SqlDbType.DateTime);
            pr[0].Value = dt;

            DataTable dbo = new DataTable();
            sqlHelper.Fill(dbo, "select *from TINHLUONG where MONTH(ThangNam)=MONTH(@LocNgay) and YEAR(ThangNam)=YEAR(@LocNgay)", pr);

            List<TINHLUONG> listTinhLuong = new List<TINHLUONG>();
            foreach (DataRow dr in dbo.Rows)
            {
                TINHLUONG tl = new TINHLUONG();
                tl.Ma_CB = dr[0].ToString();
                tl.HoTen = dr[1].ToString();
                tl.ChucVu = dr[2].ToString();
                tl.DonVi = dr[3].ToString();
                tl.HeSoLuong = float.Parse(dr[4].ToString());
                tl.LuongChinh = float.Parse(dr[5].ToString());
                tl.KhoanTru = float.Parse(dr[6].ToString());
                tl.ThucLanh = float.Parse(dr[7].ToString());
                tl.ThangNam = DateTime.Parse(dr[8].ToString());
                listTinhLuong.Add(tl);
            }
            return listTinhLuong;
        }
    }
}


